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

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.

Excelente! Muchas gracias por tu aporte, ha sido muy útil en mi trabajo.

Saludos desde Argentina.

This is fantastic!