topic Running Total - Stop at a specific date in Custom Visuals Development Discussion

Running Total - Stop at a specific date

Kim_NC — Mon, 03 Nov 2025 20:37:21 GMT

Hello. New to Power BI. I did a quick measure to create the running total for the "actual" data and it's show all months. How do I update the DAX below to stop at September but then update it to October and continue as real data is added? Thank you!

Sum of Sub System Amount running total in Fiscal Month =

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')

ISONORAFTER(

'Fiscal Period Description'[Period], MAX('Fiscal Period Description'[Period]), DESC,

'Fiscal Period Description'[Fiscal Month], MAX('Fiscal Period Description'[Fiscal Month]), DESC

)

Re: Running Total - Stop at a specific date

vivien57 — Tue, 04 Nov 2025 06:42:15 GMT

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

Re: Running Total - Stop at a specific date

Kim_NC — Thu, 06 Nov 2025 20:53:35 GMT

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())"

Re: Running Total - Stop at a specific date

vivien57 — Fri, 07 Nov 2025 07:59:07 GMT

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