Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 | |||||
|
anyone pls help on this
@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
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)
User | Count |
---|---|
88 | |
72 | |
69 | |
65 | |
57 |
User | Count |
---|---|
95 | |
93 | |
91 | |
75 | |
69 |