cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dellis81
Responsive Resident
Responsive Resident

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

4 REPLIES 4
Dellis81
Responsive Resident
Responsive Resident

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!

mwegener
Super User
Super User

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Dellis81
Responsive Resident
Responsive Resident

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors