Microsoft Office Tutorials and References
In Depth Information
Tip This scenario requires a particularly complex DAX calculation, which becomes much more
complicated if other tables are added to the model. The document available in the Microsoft
PowerPivot for Excel 2010 Data Analysis Expressions Sample (which can be downloaded at
http://tinyurl.com/DaxSample ) shows a similar example in the Time Intelligence Functions
section involving an Inventory Scenario with two tables other than the Dates table. Take a
look at that document if you have a similar scenario.
Keep in mind that the Balances[Account] column used to make the relationship with the Accounts
table is used in the LastBalanceTx formula and should not be selected in the PivotTable. Instead
of that, you should use the Accounts[Account] column; otherwise, you could see wrong data
in the PivotTable. The reason is similar to the case for which we suggest you use a Dates table
instead of denormalizing all dates information in the same table that contains the measures.
So a best practice is to hide in PivotTable all the columns that you use to relate Balances (the
table containing measures) to other tables such as BalanceDate and Accounts (which are
the tables containing attributes for browsing data).
In this chapter, you saw how to create a Dates table for a PowerPivot model and how to use
that table to support several types of calculations: number of working days, aggregation and
comparison over time, and closing balance over time.