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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dellis81
Continued Contributor
Continued Contributor

Cumulative total w/originating measure utilizing UseRelationship

Hello!

I am attempting to keep a running inventory of harvested grain, with load options.   I have all the "pieces" working, however, when I attempt to combine into a cumulative number, the measure utilizing "userelationship" is not quite right.

 

nventory = 

Var DTable = CALCULATETABLE(LoadDetail,filter(all(LoadDetail[Date]),LoadDetail[Date]<=max(LoadDetail[Date])))
Var CumDelivOut = -calculate([DeliveriesOut],filter(all(LoadDetail[Date]),LoadDetail[Date]<=max(LoadDetail[Date])))

Return
//CALCULATE([HarvestBu]+[DeliveriesIn],dtable)+CumDelivOut

CumDelivOut

 


I am using a variable "CumDelivOut (as displayed in below image).   Yhe var seems to work IF there are "harvested bu" on same day (GSF West on Sept 30th). However on Oct 1st, deliveries were out of GSF West Storage into Kxxxx Grain (6,182.91 bu) - but no harvest related loads.

 

I would expect on Oct 1st and GSF West Subtotal, Inventory column should reflect a cumulative -25,170.33.   Please help me understand why the deliveries on Oct 1st are excluded?
Harvest4.PNG

I find it intriguing the Total for all rows (Destinations) had the full 25,170.33 - which indicates I have a filter issue?

 

Link to file:  https://drive.google.com/file/d/1-dVkDxCfSpzDjl_abvdmlb5e--v_Qczl/view?usp=sharing

 

As always, support forum is awesome - thank you!

1 ACCEPTED SOLUTION

Hi @Dellis81 ,

 

Okay, your logic works with a date table.
See the attached file.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

4 REPLIES 4
Dellis81
Continued Contributor
Continued Contributor

awe, thank you! thank you! thank you!

Makes sense now!   I appreciate the Forum's support - DAX has not been an easy topic for me to grasp -and the only way to make it thru is help from the forum.   Thanks for a second look and I greatly thank you!

Hi @Dellis81 ,

 

your problem are the LoadDetail lines without "Storage".

These cannot be assigned to a destination and are only displayed in the total.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Dellis81
Continued Contributor
Continued Contributor

Thank you for taking a few minutes to review.   

I'm not understanding why/how the deliveries out column is working correctly for both days - and I take that same measure and attempt to do a cumulative total - and you will note on Sept 30th, the cumulative deliveries calculated correctly, however the following day Oct 1st, the cumulative total did not work.   The only difference, there were harvest records on the 30th, and not on the 1st. 

I will give this a few days to see if other solutions are found.   If not, appears I may need to do some "reshaping of hte data".   I don't really want to - as that only causes confusion and changes to how other users work with the data.  You know human change is hard 🙂

Hi @Dellis81 ,

 

Okay, your logic works with a date table.
See the attached file.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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