topic Cumulative Sales in DAX Commands and Tips

Cumulative Sales

JC2022 — Thu, 23 Mar 2023 15:29:35 GMT

Hi,

How do I create a cumulative Sales measure for the attached Sales table. So February is the sum of January and February. March is the sum of January, February and March. etc.

I have a few Tiers (category levels) and some filters on it.

Can you please help me out?

Re: Cumulative Sales

amitchandak — Fri, 24 Mar 2023 05:29:00 GMT

JC2022 , If this is your source format, I doubt this the best data for power bi.

First Unpivot the header and get the month and year and create a date using that

https://goodly.co.in/unpivot-data-with-2-headers/

Also, use fill down, fill up. If needed

Power Query - Fill Up Fill Down: https://youtu.be/mC2ps0pFqBI

Then you can cumulative using a date table

examples

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on net = CALCULATE([Net], Window(1,ABS,0,REL, ADDCOLUMNS(ALLSELECTED('Item'[Brand]), "_net", [Net]),ORDERBY([_net],DESC)))

Cumm Based on Brand = CALCULATE([Net], Window(1,ABS,0,REL, ADDCOLUMNS(ALLSELECTED('Item'[Brand]), "_net", [Net]),ORDERBY('Item'[Brand],asc)))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Re: Cumulative Sales

JC2022 — Fri, 24 Mar 2023 08:13:26 GMT

Hi Amit,

Thank you! the cumulative sales measure is now working but it is also summing all previous years. How do I do this per FiscalYear? So every FiscalYear starts with 0.

My formula is now:

Cumulative EUR Sales = CALCULATE(SUM(Append3[EUR Sales]),filter(all('Calendar Posting Date'[Date]),'Calendar Posting Date'[Date] <=max('Calendar Posting Date'[Date])))