Dax for Cumulative & previous Cumulative Values

[aryaps]

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

[aryaps]

anyone pls help on this

[amitchandak]

@aryaps , 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

[aryaps]

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)