Forum Discussion
Dax for Cumulative & previous Cumulative Values
Hello
How can we get the 4 Highlighted column result in BI with Dax
Purchase= Purchase Recpt & Postive Adjustment
sale = Sale & Negative Adjustments
| Entry No. | Posting Date | Entry Type | Document Type | Item No. | Quantity | Sales Amount (Actual) | Cost Amount (Actual) | Cumulative Purcahse | Previou cumulataive Purchase | Cumlative Sale | Previous Cumulative Sale |
| 1700 | 01/01/2022 | Purchase | Purchase Receipt | Mbl | 20 | 200 | 20 | ||||
| 1701 | 01/01/2022 | Postive Adjsmt | Mbl | 5 | 50 | 25 | |||||
| 1702 | 02/01/2022 | Purchase | Purchase Receipt | Charger | 100 | 1000 | 100 | ||||
| 1703 | 02/01/2022 | Negative Adjsmt | Mbl | -5 | 100 | ||||||
| 1704 | 02/01/2022 | Purchase | Purchase Receipt | Mbl | 10 | 150 | 35 | ||||
| 1705 | 02/01/2022 | Sale | Sale | Mbl | -10 | 200 | |||||
|
3 Replies
- amitchandak
Super User
Anonymous , You can have cumulative measure like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
or
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
or a day behind
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])-1 ))
or
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]) -1 ))
month behind
Cumm Sales =
var _max = max('Date'[date])
var _last = date(year(_max) , month(_max)-1, day(_max) )
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=_last ))
Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
You can also consider window function
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Thanks for the Reply
So in My case how it will , bcz Purchase= Purchase Recpt & Postive Adjustment AND sale = Sale & Negative Adjustments. apart from this, per day if multiple sale or purchase also happend so how it will come.
Note: only Entry number is different for all transactions, (Cant give index number or no time stamp available on posting date)
