You have followed the time intelligence golden rules to perfection and you think you can handle all time intelligence DAX functions. But unlike the contoso dataset you are working with actual live data from your own production environment and here you have data until the current month not the end of the year. Like this:
We want to compare the YTD from the current year to the YTD of the previous year to the current period last year. First we select the YTD of the current year by selecting the current year in the slicer and using the normal sum. Now to get the YTD of previous year we do a:
What happens here? We do the sum of table1[sales] where we use the value of datum[Date] to calculate the sum of sales one year back. What is the result:
This is not what we expected this is the sales of the entire previous year. It is much more. Why? Well one of the golden rules is that you need to have a complete contiguous time table, that means that in my seperate time table we have loaded dates until the end of the current year. And what does TOTALYTD do? According to BOL : “Evaluates the year-to-date value of the expression in the current context.” The current context is “2010” and the lastdate of our time table is 12/31/2010. Not the 6/1/2010 that is the actual last date we had data.
So here we are in a deadlock between the time intellegence golden rules and what we want. When we would use the date from the fact table it would give us the correct last date but the time intellegence functions wouldn’t return results because we wouldn’t use our seperate related time table (read here why).
So what can we do? Well we could make the formula ourself. In our case we want the sum of sales from the same period last year, that means from 1/1/2009 to 6/1/2009. We can use the DAX function DATESBETWEEN to create a custom date range. We want to get the range of datum[Date] values from the first day of the previous year:
We use the datum[Date] column here because we know this is a coninues data range and it would always contain 1/1. Using dateadd we go back 12 months and firstdate makes sure we get the first value from this range. To select the date we want the calculation to end we use:
Here we use the lastdate of the fact table to determine the same date one year back, in our case 6/1/2009.
The total formula would look like:
=CALCULATE(sum(Table1[sales]), DATESBETWEEN(datum[Date], FIRSTDATE(DATEADD(datum[Date],-12,MONTH)), LASTDATE(DATEADD(Table1[Date],-12,MONTH)) ))
PowerPivot DAX will calculate the sum of Table1[sales] from a date range between 1/1/2009 and 6/1/2009.
Which gives us the result we want: