Cumulative Totals Not Working Correctly

[jdperry]

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!

[Ashish_Mathur]

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

[jdperry]

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

[v-xicai]

Hi @jdperry ,

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.

[jdperry]

Hi Amy,

Right now I'm using this:

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

and 

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

This still results in a weird result as shown above (that set of unexpected 8's in the AP Cumulative Qty and the incorrect totals in the AB Cumulative Qty.

[v-xicai]

Hi @jdperry,

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.

[jdperry]

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