Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Cumulative Totals Not Working Correctly

I'm seeing some odd behavior when trying to calculate the cumulative totals of activities completed in a project schedule.  I have two relationships between the data table and the date table, an active one between the date and the As-Planned finish dates, and an inactive one between the date and the As-Built finish dates.  As you can see below, the As-Planned Cumulative Quantity appears to be computing correctly, but the AS-Built Cumulative Quantity is not.

 

I'm using the following measures on the As-Built Side:

As-Built Activity Count = CALCULATE(COUNT(AP[AB Finish]), USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]))
AB Cumulative Qty = CALCULATE([As-Built Activity Count],USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]),FILTER(ALL('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))
 
I'll also note that the AB Cumulative Qty seems to calculate okay when it's not filtered.  Once I start filtering it I start seeing the odd behavior below.  Any thoughts, anyone?  Thanks!

 

Need Help

6 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous,

     

    From your screenshot above, find [AP Cumulative Qty] is not Cumulative total. You can try measures below.

     

    AP Cumulative Qty= CALCULATE(SUMX(AP,[As-Planned Activity Count]),FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))

     

    AB Cumulative Qty = CALCULATE(SUMX(AP,[As-Built Activity Count]),USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]),FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Amy,

       

      I tried adding the allselected clause to the filter and it didn't end up changing anything.  The values in the table remained the same.  Any other thoughts?

       

      Thanks!

       

      -Jon

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Try this

    AB Cumulative Qty = CALCULATE([As-Built Activity Count],USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]),DATESBETWEEN('Date Table'[Date],MINX(ALL('Date Table'),'Date Table'[Date]),MAX('Date Table'[Date])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      That one didn't work either.  The cumulatives still add up to less than the sum of the AB quantity.

      • v-xicai's avatar
        v-xicai
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous ,

         

        Have you used" SUMX(AP,[As-Built Activity Count] " in measures, please add and try it again.

         

        Best Regards,

        Amy

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.