Categories
PowerBI

Running totals in PowerBI

How to use running totals in PowerBI using 3 methods: Quick measures, DAX formulas and DAX formula with ISONORAFTER

When we want to see the evolution of the data through the time and see how it increase in total, we should use Running Totals, a Running Total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence.

Power BI offers us to do it in several ways, the simplest is:

Create a running total with a quick measure

1. Create a new quick measure.

2. In Calculation select Running Total

3) On Base value we must write the value whose running total we want. Secondly, in Field we will write the running field which will be resetting each time this field repeats, for example, a date.

And click the OK button.

4) To see the running total we must add a visualization, like a line chart, with the new created measure.

5) In the Axis we must put the field that we chosen before.

**We have to be very careful because if we put in Axis a field that is not the chosen one, the chart will be complete wrong.

**

6) Our total running would be ready

Another way to do a running total is making by ourselves the measure.

ALLSELECTED: Removes all context filters in the table except filters that have been   applied to the specified columns.

FILTER: Returns a table that represents a subset of another table or expression, the second parameter of this function is important, because it define when the sum will be resetting.

CALCULATE: Evaluates an expression in a context modified by filters.

  • In the all cases we will have to use:
RunningTotal =  
CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable)
        )
    )
  • There will be times that we are only interested in the calculations being propagated until today, to do that we can use:
RunningTotal =  
IF ( MAX(DateTable[Date] ) > TODAY(),
    BLANK(), 
    CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable)
        )
    ) 
)
  • If we want additional resetting sum conditions we could add more filters
RunningTotal =  
IF ( MAX(DateTable[Date] ) > TODAY(),
    BLANK(), 
    CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable) && [MoreFilters]
        )
    ) 
)

*For example when we want to reset each year we can use:

RunningTotal =  
IF ( MAX(DateTable[Date] ) > TODAY(),
    BLANK(), 
    CALCULATE(
        <BaseValue>,
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= MAX(DateTable)
	                   && DateTable[Year] == MAX(DateTable[Year])
        )
    ) 
)

ISONORAFTER

Some examples on internet use the function ISONORAFTER that takes a variable number of triples, the first two values are the expressions to be compared, and the third parameter indicates the sort order. The sort order can be ascending or descending.

The first parameter is compared with the second parameter, based on the sort order. If the sort order is ascending the first parameter have to be less than or equal to the second parameter. If the sort order is descending, the second parameter have to be greater than or equal to the first parameter.

ISONORAFTER function returns TRUE if any of the comparisons is TRUE, otherwise returns FALSE.

RunningTotal= 
CALCULATE(
    <BaseValue>,
    FILTER(
        ALLSELECTED([Date]),
        ISONORAFTER([Date], MAX([Date]), DESC)
    )
)

https://www.tutorialspoint.com/dax_functions/dax_isonorafter_function.htm