Forum Discussion

sadiahsapiee's avatar
sadiahsapiee
Icon for Helper I rankHelper I
6 years ago
Solved

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 
Need Help
  • sadiahsapiee's avatar
    sadiahsapiee
    6 years ago

    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.

10 Replies

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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
    • sadiahsapiee's avatar
      sadiahsapiee
      Icon for Helper I rankHelper I
      6 years ago

      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. 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      sadiahsapiee
      Icon for Helper I rankHelper I
      6 years ago

      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
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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