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

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.

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

      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.