cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sadiahsapiee
Frequent Visitor

Cumulative value from measure column not correct

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

 

Confirm Del Plan =
IF ([Confirmed Delivery Plan] = BLANK(); Sum(TableA[po_qty]);
SUM(TableB[del_qty])
)
CDP =
CALCULATE([Confirm Del Plan];
FILTER(ALLSELECTED('Date');
'Date'[DateKey] <=MAX('Date'[DateKey]))
)
 
Appreciate anyone can help to detect what is wrong. Thanks in advance 
1 ACCEPTED SOLUTION

Finally  I got the solution

 

Cumulative CDP = SUMX(

  FILTER (ALLSELECTED('Date');

'Date'[DateKey] <=MAX('Date'[Datekey]));

[Confirm Del Plan]

)

 
Thanks to all to Paul and Kentyler for the suggestion and idea.

View solution in original post

10 REPLIES 10
V-pazhen-msft
Community Support
Community Support

@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

descriptiondatepo_qtyYearWeekNum
AirScale23 December 20190201952
AirScale16 December 20190201951
AirScale23 December 2019235201952
AirScale09 December 2019282201950
AirScale16 December 20192548201951
AirScale02 December 2019139201949
AirScale02 December 20190201949
AirScale02 December 20190201949
AirScale02 December 20190201949
AirScale02 December 20190201949
AirScale16 December 20190201951
AirScale09 December 20190201950
AirScale23 December 20190201952
AirScale16 December 20190201951
AirScale09 December 20190201950
AirScale16 December 20190201951
AirScale09 December 20190201950
AirScale02 December 20190201949
AirScale09 December 20190201950
AirScale23 December 20190201952
AirScale02 December 20190201949
AirScale02 December 20190201949
AirScale23 December 20190201952
AirScale16 December 20190201951
AirScale09 December 20190201950
AirScale23 December 20190201952
AirScale23 December 20190201952
AirScale16 December 20190201951
AirScale09 December 20190201950
AirScale09 December 20190201950
AirScale23 December 20190201952
AirScale16 December 20190201951

 

Table B

part_descriptiondatedel_qtyYearWeeknum
AirScale10 February 202020020207
AirScale03 February 202020020206
AirScale27 January 202020020205
AirScale20 January 202020020204
AirScale13 January 202020020203
AirScale06 January 202016020202
AirScale16 December 2019600201952
AirScale10 February 202088920207
AirScale03 February 202057420206
AirScale27 January 202057320205
AirScale20 January 202050020204

Hi @sadiahsapiee 


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.

 

 
Best, 
Paul
 
 

Hi

Below is the formula I use 

 

Confirmed Delivery Plan = Sum(TableB[del_qty])
Confirm Del Plan =
IF ([Confirmed Delivery Plan] = BLANK(); Sum(TableA[po_qty]);
    SUM(TableB[del_qty])
)
 
CDP = 
CALCULATE([Confirm Del Plan]; 
    FILTER(ALLSELECTED('Date');
     'Date'[DateKey] <=MAX('Date'[DateKey]))
)

Hi,

 

not right.JPG
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

Spoiler
 

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]

)

 
Thanks to all to Paul and Kentyler for the suggestion and idea.

finally I found the solution by changing the formula like this

-> **bleep** CDP =SUMX(
   FILTER(ALLSELECTED('Date');
   'Date'[DateKey] <=MAX('Date'[DateKey]));
   [Confirm Del Plan]
)
 
The table shows the cumulative value correctly. 

Thanks to all that gave idea and suggestion. 

 

kentyler
Solution Sage
Solution Sage

You need to add a filter for the "del plan" to your CALCULATE

Something like this

CDP =
VAR DelPlan = MAX(tableb[Del Plan])
RETURN CALCULATE([Confirm Del Plan];
FILTER(ALLSELECTED('Date');
'Date'[DateKey] <=MAX('Date'[DateKey]);
tableb[Del Plan] = DelPlan
)
)
I just winged this. I did not create a table to test it. You capture the current row plan in a variable, and then filter your aggregation to only total for rows that have that same plan.

I learn something every time I answer a question




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Hi Kentyler,

 

Thank for your reply. 

I still did not get it -add filter for 'Del plan' to the CALCULATE

CDP =
VAR DelPlan = MAX(tableb[Del Plan])
RETURN CALCULATE([Confirm Del Plan];
FILTER(ALLSELECTED('Date');
'Date'[DateKey] <=MAX('Date'[DateKey]);
tableb[Del Plan] = DelPlan
 
tableb we calculate del_qty for same week & item. MAX(tableb[Del Pan] is it Date or del_qty? I tried both did not work. 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors