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

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