Running Total - Stop at a specific date

[Kim_NC]

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

        )

    )

)

[vivien57]

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