Accumulate values (running value) over multiple years in PowerPivot using DAX

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:

=SUMX(all(dimdate[caledaryear]),TOTALYTD(sum(FactSales[SalesAmount]), DimDate[Datekey])

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())

The function works as follows: Calculate the sum of FactSales[SalesAmount] of all the dates between the first date of all the sales using the all(factsales[datekey]) to the lastdate in Pivottable context. If you put month on rows the current context is all the dates in the months that is selected, lastdate gives us the last day in the month on that row.

You could also set a hard start date by replacing the start date with DATE(2010,1,1) for a start date of 1/1/2010.

This is the result:

5 Replies to “Accumulate values (running value) over multiple years in PowerPivot using DAX”

Well timed blo entry, I’m in the process of compiling a model which does just that.
Amazing how simple a solution can be when you know 🙂

Hello,

Very useful blog. Could I just ask if its possible to create a running total for all data in the underlying dataset (ie in the underlying PowerPivot Window) as opposed to a subset that I have shown using a filter? Practically – I have to use this running total as an opening balance – but cant see a way of getting to it. Thanks in advance for any assistance.

Hello,

I’m working on this model where i have to calculate accumulative values over dates but the problem is that what im trying to accumulate are measure that ive already made over my data. Is this possible?

Thanks.