Cumulative Total from a Summarised Table

[2NV_DB]

Hello All,

I have the followed the information provided in this thread to create a summary table with a cumulative total here: https://community.fabric.microsoft.com/t5/Desktop/Cumulative-Using-Summarized-Table/td-p/1355321

However my cumulative value will not result and I don't understand why. It should be the cumulative of the Incremental Sales column.


I expect the cumulative to be 80764+208746 etc. The Incremental Sales is simply 2024-2023 month.

What am I doing wrong?
Thanks

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.

I suggest having a [month number] column as well.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result table =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            consolidated_sales_actual,
            fianacial_calendar[Month],
            fianacial_calendar[Month Number]
        ),
        "2023sales",
            CALCULATE (
                SUM ( consolidated_sales_actual[sales_net] ),
                fianacial_calendar[Year] = 2023
            ),
        "2024sales",
            CALCULATE (
                SUM ( consolidated_sales_actual[sales_net] ),
                fianacial_calendar[Year] = 2024
            )
    )
VAR _diff =
    ADDCOLUMNS ( _t, "diff", [2024sales] - [2023sales] )
VAR _cumulatediff =
    ADDCOLUMNS (
        _diff,
        "cumulatedifference",
            SUMX (
                WINDOW (
                    1,
                    ABS,
                    0,
                    REL,
                    _diff,
                    ORDERBY ( fianacial_calendar[Month Number], ASC )
                ),
                [diff]
            )
    )
RETURN
    _cumulatediff

[2NV_DB]

Wow thank you! Not sure how, but this this works well! I'm not familiar with the Window function.