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?
I find it intriguing the Total for all rows (Destinations) had the full 25,170.33 - which indicates I have a filter issue?
As always, support forum is awesome - thank you!
Solved! Go to Solution.
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!
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 🙂
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.