Cumulative value from measure column not correct

[sadiahsapiee]

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 

[V-pazhen-msft]

@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

[sadiahsapiee]

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

[V-pazhen-msft]

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

[sadiahsapiee]

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]))

)

[V-pazhen-msft]

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

[sadiahsapiee]

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.

[sadiahsapiee · 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.

[sadiahsapiee]

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]

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

I'm a personal Power BI trainer 

[sadiahsapiee]

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.