Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Running Total between two dates

Hi All,   I have a running total calculated for use on a bar/line combo chart using the built-in running total quick measure on my revenue column of a table. I have a relationship active between my...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Try like

     

    REVENUE CM running total in Date =

    var _max = maxx(allselected('Actuals'), 'Actuals'[Date])

    return
    CALCULATE(
    SUM('Actuals'[REVENUE CM]),
    FILTER(
    ALLSELECTED('FY Calendar'[Date]),
    ISONORAFTER('FY Calendar'[Date], MAX('FY Calendar'[Date]), DESC) && 'FY Calendar'[Date] <= _max
    )
    )

     

    or

     

    REVENUE CM running total in Date =

    var _max = maxx(allselected('Actuals'), 'Actuals'[Date])

    return if( max( 'FY Calendar'[Date]) <= _max 
    CALCULATE(
    SUM('Actuals'[REVENUE CM]),
    FILTER(
    ALLSELECTED('FY Calendar'[Date]),
    ISONORAFTER('FY Calendar'[Date], MAX('FY Calendar'[Date]), DESC)
    )
    ), blank())

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try like

 

REVENUE CM running total in Date =

var _max = maxx(allselected('Actuals'), 'Actuals'[Date])

return
CALCULATE(
SUM('Actuals'[REVENUE CM]),
FILTER(
ALLSELECTED('FY Calendar'[Date]),
ISONORAFTER('FY Calendar'[Date], MAX('FY Calendar'[Date]), DESC) && 'FY Calendar'[Date] <= _max
)
)

 

or

 

REVENUE CM running total in Date =

var _max = maxx(allselected('Actuals'), 'Actuals'[Date])

return if( max( 'FY Calendar'[Date]) <= _max 
CALCULATE(
SUM('Actuals'[REVENUE CM]),
FILTER(
ALLSELECTED('FY Calendar'[Date]),
ISONORAFTER('FY Calendar'[Date], MAX('FY Calendar'[Date]), DESC)
)
), blank())

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Superb - second one worked perfectly, just had to add in a comma after _max in the if statement.