Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nauriso1
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 totalRunning 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

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!

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

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!

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

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!

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?

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.