topic Re: Cumulative Total from a Summarised Table in DAX Commands and Tips

Cumulative Total from a Summarised Table

2NV_DB — Wed, 05 Jun 2024 03:45:51 GMT

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

Re: Cumulative Total from a Summarised Table

Jihwan_Kim — Wed, 05 Jun 2024 04:08:05 GMT

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

Re: Cumulative Total from a Summarised Table

2NV_DB — Wed, 05 Jun 2024 05:06:35 GMT

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