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

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