Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Why my DAX to calculate cumulative not continue from previous row?
Week Confirm Del Plan CDP
2019/49 139 <- TableA 139
2019/50 281 <- TableA 421
2019/51 600 <-TableB 600 <- start new value
2019/52 160 <-TableB 760
Solved! Go to Solution.
Finally I got the solution
Cumulative CDP = SUMX(
FILTER (ALLSELECTED('Date');
'Date'[DateKey] <=MAX('Date'[Datekey]));
[Confirm Del Plan]
)
@sadiahsapiee
You can give it a try with the previous var, it makes sence for me. And If you would like a solid solution afterward, we would need some data to see the columns in Table A and Table B.
Paul
Here is the file sample
Table A
description | date | po_qty | Year | WeekNum |
AirScale | 23 December 2019 | 0 | 2019 | 52 |
AirScale | 16 December 2019 | 0 | 2019 | 51 |
AirScale | 23 December 2019 | 235 | 2019 | 52 |
AirScale | 09 December 2019 | 282 | 2019 | 50 |
AirScale | 16 December 2019 | 2548 | 2019 | 51 |
AirScale | 02 December 2019 | 139 | 2019 | 49 |
AirScale | 02 December 2019 | 0 | 2019 | 49 |
AirScale | 02 December 2019 | 0 | 2019 | 49 |
AirScale | 02 December 2019 | 0 | 2019 | 49 |
AirScale | 02 December 2019 | 0 | 2019 | 49 |
AirScale | 16 December 2019 | 0 | 2019 | 51 |
AirScale | 09 December 2019 | 0 | 2019 | 50 |
AirScale | 23 December 2019 | 0 | 2019 | 52 |
AirScale | 16 December 2019 | 0 | 2019 | 51 |
AirScale | 09 December 2019 | 0 | 2019 | 50 |
AirScale | 16 December 2019 | 0 | 2019 | 51 |
AirScale | 09 December 2019 | 0 | 2019 | 50 |
AirScale | 02 December 2019 | 0 | 2019 | 49 |
AirScale | 09 December 2019 | 0 | 2019 | 50 |
AirScale | 23 December 2019 | 0 | 2019 | 52 |
AirScale | 02 December 2019 | 0 | 2019 | 49 |
AirScale | 02 December 2019 | 0 | 2019 | 49 |
AirScale | 23 December 2019 | 0 | 2019 | 52 |
AirScale | 16 December 2019 | 0 | 2019 | 51 |
AirScale | 09 December 2019 | 0 | 2019 | 50 |
AirScale | 23 December 2019 | 0 | 2019 | 52 |
AirScale | 23 December 2019 | 0 | 2019 | 52 |
AirScale | 16 December 2019 | 0 | 2019 | 51 |
AirScale | 09 December 2019 | 0 | 2019 | 50 |
AirScale | 09 December 2019 | 0 | 2019 | 50 |
AirScale | 23 December 2019 | 0 | 2019 | 52 |
AirScale | 16 December 2019 | 0 | 2019 | 51 |
Table B
part_description | date | del_qty | Year | Weeknum |
AirScale | 10 February 2020 | 200 | 2020 | 7 |
AirScale | 03 February 2020 | 200 | 2020 | 6 |
AirScale | 27 January 2020 | 200 | 2020 | 5 |
AirScale | 20 January 2020 | 200 | 2020 | 4 |
AirScale | 13 January 2020 | 200 | 2020 | 3 |
AirScale | 06 January 2020 | 160 | 2020 | 2 |
AirScale | 16 December 2019 | 600 | 2019 | 52 |
AirScale | 10 February 2020 | 889 | 2020 | 7 |
AirScale | 03 February 2020 | 574 | 2020 | 6 |
AirScale | 27 January 2020 | 573 | 2020 | 5 |
AirScale | 20 January 2020 | 500 | 2020 | 4 |
Where does the "Confirmed Delivery Plan(measure?)" come from, you used it to create "Confirm Del Plan" in your formula before calculating CDP.
I am kind of confused with your description. If possible, please share your pbix or just clarity each steps you have done.
Hi
Below is the formula I use
Hi,
I don't think I have the correct formula or data, and I don't see the Date table and Datekey column that you used in cumulative calculation.
Best,
Paul
Hi,
Actually, I have Date table which DateKey link to Date column in both Table A and TableB.
Relationship is already created Date[DateKey]<- TableA[Date] & Date[Datekey]<->TableB[Date]. So the Year/Week is from Date table.
Finally I got the solution
Cumulative CDP = SUMX(
FILTER (ALLSELECTED('Date');
'Date'[DateKey] <=MAX('Date'[Datekey]));
[Confirm Del Plan]
)
finally I found the solution by changing the formula like this
Thanks to all that gave idea and suggestion.
You need to add a filter for the "del plan" to your CALCULATE
Something like this
Help when you know. Ask when you don't!
Hi Kentyler,
Thank for your reply.
I still did not get it -add filter for 'Del plan' to the CALCULATE
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |