Running Max/Min/Avg of Running Total

Anonymous — Tue, 25 Apr 2017 16:34:56 GMT

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.

topic Running Max/Min/Avg of Running Total in Quick Measures Gallery

Running Max/Min/Avg of Running Total