A question on the PowerPivot for Excel forum piked my interest: Cannot Accumulate Values Over Multiple Years. What Jon here was trying to do was do a a running values over multiple years. Running values over a single year are easy using the TotalYTD value. But can we do this over multiple years?
The first thing i tried to use was SUMX where i want to calculate the YTD for each year:
But this gives me the same result as a regular YTD does. And this is because SUMX responds to pivot context just like anything else. For a moving average over a custom time span we need to make a function of our own. To get the result we want we need to change the context to not only include the current period but also all periods leading up to this period. We can use the DAX CALCULATE function to change the context of our measure and the DATESBETWEEN to get the date range.
This results in the following function:
=if(sum(FactSales[SalesAmount]) > 0, Calculate(sum(FactSales[SalesAmount]), DATESBETWEEN(DimDate[Datekey],FIRSTDATE(all(FactSales[DateKey])), LASTDATE(FactSales[DateKey]))) ,Blank())