cancel
Showing results for 
Search instead for 
Did you mean: 

Running Max/Min/Avg of Running Total

Advisor
316 Views
Highlighted
TateB
Advisor

Running Max/Min/Avg of Running Total

This measure calculates the running max/min/average (depending on your requirement) of a running total based on the dynamic date range selected.  

 

To recap, here is the Running Total quick measure that Power BI gives us.  A measure like this should be be present in your model.

Running Total := 
CALCULATE(
	'Sales'[Amount],
	FILTER(
		ALLSELECTED('Date'[Date]),
		ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
	)
)

Below is an example of how to set up a running MAX of the running total shown above:

 

Max of Running Total :=
VAR __INTERIM_CALC =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( 'Date' ), 'Date'[Date] ),
        "__RunningTotal", [Running Total]
    )
RETURN
    MAXX (
        FILTER (
            __INTERIM_CALC,
            ISONORAFTER ( 'Date'[Date], MAX ( 'Date'[Date] ), DESC )
        ),
        [__RunningTotal]
    )

 

To find the running average/minimum/etc. of the running total, just change the MAXX in the above query to AVERAGEX, MINX, or another AGGX function.