cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nauriso1 Frequent Visitor
Frequent Visitor

Sum of n days Running Total

Hello PowerBI experts!

 

 

How to calculate n days sum of all running totals using measure? For an example I have such table and I want caculate what would be the sum of all running total values in prevous days in day 10 (150). This would be something like running total of running total:

running_total.PNGRunning total

Values in column "Running total" is calculated using formula:

Running Total:=CALCULATE(SUM(fct[Amount]);
FILTER(ALL(dimDays);dimDays[DayId]<=MAX(dimDays[DayId]))

 

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Sum of n days Running Total

Hi @nauriso1,

 

Please try these two measures

 

Running Total = CALCULATE(SUM(fct[Amount]),fILTER(ALL(fct),'fct'[DayID]<=MAX('fct'[DayID])))+0

and

 

Sum Running Total = CALCULATE(
		SUMX(
			FILTER(
				ALL('fct'),
				'fct'[DayID]<=MAX('fct'[DayID])
				),
				[Running Total])
				)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

5 REPLIES 5
Highlighted
Phil_Seamark Super Contributor
Super Contributor

Re: Sum of n days Running Total

Hi @nauriso1

 

I used this for my running total (as a calculated column on fct)

 

Running Total = CALCULATE(
    SUM(
        fct[Amount]),
        FILTER(
            ALL('fct'),
            'fct'[DayID]<=EARLIER('fct'[DayID])
            )
           )

And this for the Sum of Running Total

 

Sum of Running Totals = 
    CALCULATE(
        SUM(fct[Running Total]),
        FILTER(
            ALL('fct'),
            'fct'[DayID]<=EARLIER('fct'[DayID])
            )
           )

 

This was my result

 

 Running Total.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

nauriso1 Frequent Visitor
Frequent Visitor

Re: Sum of n days Running Total

Thanks Phil for your answer, but I need measure not calculated column. 

Phil_Seamark Super Contributor
Super Contributor

Re: Sum of n days Running Total

Hi @nauriso1,

 

Please try these two measures

 

Running Total = CALCULATE(SUM(fct[Amount]),fILTER(ALL(fct),'fct'[DayID]<=MAX('fct'[DayID])))+0

and

 

Sum Running Total = CALCULATE(
		SUMX(
			FILTER(
				ALL('fct'),
				'fct'[DayID]<=MAX('fct'[DayID])
				),
				[Running Total])
				)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

nauriso1 Frequent Visitor
Frequent Visitor

Re: Sum of n days Running Total

Thanks @Phil_Seamark, that is what I was looking for. One question, why did you add a zero at the end of the Running Total formula?

Phil_Seamark Super Contributor
Super Contributor

Re: Sum of n days Running Total

Hi @nauriso1,

 

Just to force it to return a result for the day for the grid-visual I was using it.  It doesn't affect the formula, so feel free to remove if you prefer.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!