To make use of Time intelligent functions in PowerPivot it is recommended to create a separate related time table. This will make sure you don’t need all kind of tricks to make sure you have date, check out this blog post to see why we need a related time table in more detail. This blog post will describe how to create a separate related time table, the easy way.
Lets say we have the following data:
We have a year and we have a monthnumber of year. We want to do time intelligent functions on this data, to be able to do this we need a date column. We have to create one in this scenario, we create a new column with the following dax function:
We don’t have a day column available so i always use the first day of the month, we now have a date column to work our time function on. But because the fact and date columns are in the same table dax has trouble using these two in combination. To make this easier you can create a related separate time table.
Select your two date columns in PowerPivot:
Copy and paste them in new worksheet in Excel, go to data, remove duplicates
This will create a unique set of values. Create a table of the values (Ctrl – L) and use create linked table to load this data to PowerPivot, we also create a date column in this table:
We now have separate time table available, now we create a relationship between them where the new date table is the lookup table.
We now can use these in our dax functions, in CTP a function would look like this:
=CALCULATE(sum(Table2[Values]), DATESYTD(‘Date'[Date]), all(‘Date’))
I use the fields from the new date table instead of the fact table.
In RTM you can just do (again using the date column from the date table):
I hope this will help in working with time intelligent functions. This is of course but one of the ways to create this table, but this one is pretty easy to implement.