Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jdperry
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!

 

Cumulate Oddness.png

6 REPLIES 6
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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

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.

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

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.