Ok remember the Time intelligent function golden rules ? One of the key items there is :
- Always create a separate Time table.
And why is that you think? Well i got a question about someone doing a previous year but got only values until the current month he has data, like this:
And this makes sense, because in this function I use the data from the fact table:
=CALCULATE(sum(Table1[Value]),DATEADD(Table1[Date],-12,MONTH), ALL(Table1))
When the function walks through the Table1[Date] values of the fact table to calculate the previous year he stops at Month 4 of 2007 because he cannot make data up which isn’t there.
When we would use a separate related timetable which holds data from the future using the following statement:
=CALCULATE(sum(Table1[Value]),DATEADD(DimDate[Datekey],-12,MONTH))
we get:
]
It now uses the date value from the dimdate table to run his loop at. Since this column does have values in the future it will keep on going until the end of the date table and thus finding values months from now.