cancel
Showing results for
Did you mean: Frequent Visitor

## 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! 6 REPLIES 6  Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com Frequent Visitor

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

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. Frequent Visitor

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.  Community Support

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. Frequent Visitor

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  