Forum Discussion
Helper IIICumulative Sales
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?
2 Replies
- amitchandak
Super User
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
JC2022Hi 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])))
