Running Total between two dates

[Anonymous]

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 revenue table and a dedicated Date table. The problem I'm having is, my Financial year runs from October 1st - September 30th, and therefore only have revenue finalised up until a certain point. When I calculate the running total, the calculation stops where the revenue finishes in the data and leaves a horizontal line on my line chart with the current total. How do I only calculate up until a certain point, or between two dates to prevent this happening, as the outcome is very messy. I've highligted the outcome below:

My current measure's DAX is as follows:

REVENUE CM running total in Date =

CALCULATE(

    SUM('Actuals'[REVENUE CM]),

    FILTER(

        ALLSELECTED('FY Calendar'[Date]),

        ISONORAFTER('FY Calendar'[Date], MAX('FY Calendar'[Date]), DESC)

    )

)

[amitchandak · Accepted solution]

@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]

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