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:
Date =DATE(Table2[CalendarYear],Table2[MonthNumberOfYear],”01″)
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):
CALCULATE(sum(Table2[Values]), DATESYTD(‘Date'[Date]))
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.
Merci !
A big help! And how will my Dutch co-worker ever forgive me for mixing Danish with Dutch?!
Time to give my pivot table another kick at the can.
My sales table has 7 years of data. Rather than just copying it out and pulling out unique dates only, I created a table listing 4000+ dates sequentially from 2000 to 2019 (I like to be pro-active). I’ve tried ytd and parallelperiod but no success so far. I’ll take your fine example and try again.
Question – Can you think why this wouldn’t work, just to have a defined (complete) list of dates as my Date source table?
@johncon
np, a mistake very common for Americans to make 🙂
One thing i can think of is that the relationship doesn’t find any values. Make sure your datetime column from both tables are really the same (down to the second).
And make sure you use the month / year values from your date table inside your pivottable.
You could send me your xlsx so i can take a look. Or send me a mail with more information to kasper at kasperonbi.com
Kasper
In the words of Borat – Great Success! I had it right all along (well close) but my filters were somehow blocking the results. I have it on current, which is 2010. Nothing was being shown. As soon as I went to a previous year, 2008, 2009, etc, – Previous sales were shown. Not 100% accurate – but that’s another story.
I’ll post another question/comment in the PowerPivot forum. Thank you for your blogs and support!