Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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

 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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)