Forum Discussion
Running Total - Stop at a specific date
Hello Kim_NC ,
Please note that your months are not sorted, so I'm not sure that the totals you want will be calculated correctly.
But otherwise, you can try this. It assumes that you have a column that indicates whether the data is real or not ('Combined'[IsActual] = TRUE())
Sum of Sub System Amount running total in Fiscal Month =
VAR MaxActualMonth =
CALCULATE(
MAX('Fiscal Period Description'[Fiscal Month]),
FILTER('Combined', 'Combined'[IsActual] = TRUE())
)
RETURN
CALCULATE(
SUM('Combined'[Sub System Amount]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Fiscal Period Description',
'Fiscal Period Description'[Period],
'Fiscal Period Description'[Fiscal Month]
),
ALLSELECTED('Fiscal Period Description')
),
'Fiscal Period Description'[Fiscal Month] <= MaxActualMonth &&
ISONORAFTER(
'Fiscal Period Description'[Period], MAX('Fiscal Period Description'[Period]), DESC,
'Fiscal Period Description'[Fiscal Month], MAX('Fiscal Period Description'[Fiscal Month]), DESC
)
)
)- MaxActualMonth retrieves the last month for which actual data exists.
- The Fiscal Month <= MaxActualMonth filter prevents the cumulative total from exceeding this month.
- The rest of the code retains your cumulative total logic.
Please feel free to give a kudo and mark this as the answer if it helped you.
have a nice day,
Vivien
Hi vivien57 ,
Thank you for helping me with this issue. The reason why the months are not in order is because it is ordered in Fiscal Year and not Calendar Year. Your suggestion did stop the Actual line at September for 2026 but the 2025 line is no longer cumulative for each month. How can I update the DAX so it runs cumulatively?
I don't understand what you mean by "column that indicates whether the data is real or not column that indicates whether the data is real or not ('Combined'[IsActual] = TRUE())"
Hello Kim_NC ,
The behaviour you are observing is due to the fact that the modified measure filters months based on the maximum month with actual data, but it does not do so by year. So when you want to display cumulative data for 2025, it is blocked by the condition that limits it to September 2026.
Try this :- Calculates the maximum month per year for actual data.
- Applies the cumulative filter per year.
Sum of Sub System Amount running total in Fiscal Month = VAR CurrentYear = MAX('Fiscal Period Description'[Fiscal Year]) VAR MaxActualMonth = CALCULATE( MAX('Fiscal Period Description'[Fiscal Month]), FILTER( 'Combined', 'Combined'[IsActual] = TRUE() && 'Fiscal Period Description'[Fiscal Year] = CurrentYear ) ) RETURN CALCULATE( SUM('Combined'[Sub System Amount]), FILTER( CALCULATETABLE( SUMMARIZE( 'Fiscal Period Description', 'Fiscal Period Description'[Period], 'Fiscal Period Description'[Fiscal Month], 'Fiscal Period Description'[Fiscal Year] ), ALLSELECTED('Fiscal Period Description') ), 'Fiscal Period Description'[Fiscal Year] = CurrentYear && 'Fiscal Period Description'[Fiscal Month] <= MaxActualMonth && ISONORAFTER( 'Fiscal Period Description'[Period], MAX('Fiscal Period Description'[Period]), DESC, 'Fiscal Period Description'[Fiscal Month], MAX('Fiscal Period Description'[Fiscal Month]), DESC ) ) )- If you do not have “IsActual”, we can replace the logic with a check for the presence of data (NOT(ISBLANK(“Combined”[Sub System Amount]))).
Please feel free to give me a kudo and mark my answer as the solution if it helped you.
have a nice day,
Vivien
