PowerPivot Time Calculations

A recommended practice for implementing time calculations in PowerPivot is to have a Data table with a datetime column. Kasper de Jonge explains in more details in this blog. This approach will probably save effort when importing data from normalized schemas and won’t require specifying additional arguments to the PowerPivot time functions. However, it will undoubtedly present an issue when importing data from a star schema. A dimensional modeling best practice is to have an integer key for a Data dimension table in the format YYYYMMDD and integer foreign keys in the fact tables. Luckily, you don’t have to normalize data back to datetime when building a PowerPivot model on top of star schemas after the issue with the All filter Kasper reported a while back got fixed in PowerPivot RTM.

Let’s consider the AdventureWorksDW schema. Its DimDate table has an integer key (DateKey). Let’s say you import this table in PowerPivot and name it Date. This is what the resulting PowerPivot table may look like. Fact tables join the Data table on the DateKey field which is an integer key.

Now you want to calculate YTD values. Since we don’t have scope assignments in PowerPivot, you need to implement a time calculation for each field that you need YTD. For the SalesAmount field, you can use the following DAX expression:

=TotalYTD(Sum([SalesAmount]), ‘Date'[Date], All(‘Date’))

The TotalYTD function is one of the PowerPivot time intelligence functions. The second argument references the Date column in the Date table which must be of datetime. Finally, the third argument overwrites the evaluation context by telling PowerPivot to evaluate the expression irrespective of date selection, that is across all dates. A nice enhancement for a future PowerPivot (aka BISM) release is to provide some sort of UI to allow the modeler to flag which column in the Data table is a datetime column in order to avoid the All flag.