Forum Discussion

Kim_NC's avatar
Kim_NC
New Member
10 months ago

Running Total - Stop at a specific date

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 Oc...
Cumulative Total
Running Totals
Kim_NC's avatar
Kim_NC
New Member
10 months ago

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

 

vivien57's avatar
vivien57
Icon for Super User rankSuper User
10 months ago

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