Forum Discussion
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 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:
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())
2 Replies
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())