Forum Discussion
Need DAX help with Inventory on Hand
Thanks. I tried using something like this with a sample Date of June 1, 2019. In my real fact table there are about 10-20k equipment IDs with Create Dates before that date. Again Jun 1 is just an example - it should be similar with any date
OK I'm less clear on what you are asking!
The last measure I gave you would calculate the inventory on hand for any given date you choose. But from your table I'm thinking what you are actually asking for is a measure that would calculate the inventory you had on hand, for every date in your data? Is that right?
- Anonymous6 years agoNot applicable
Yes that is correct. Assume a fact table with thousands of rows with an EquipmentID, a date it was created (i.e. CreateDate), a StatusDate for when the status changes, and a status description (i.e. Active, Removed). So if we pick a date of June 1, 2019 and look in the fact table we should see several rows for different Equipment IDs with a CreateDate of Jun 1, 2019 meaning they were placed into inventory on that date. But what about the thousands of Equipment IDs that were placed into Inventory on earlier days. They are still in Inventory. For example, a piece of equipment with a CreateDate of Mar 14, 2018 with no status change is still in inventory on Jun 1, 2019. I would like to count ALL of the pieces of equipment in inventory for each day. Hopefully this makes sense.
- Anonymous6 years agoNot applicable
Edit: I posted this as you posted your response - I think this should be what you need;
If that is what you are after, you could use a running total that excludes all "removed" items. Something like:
Inventory on hand by day = CALCULATE( COUNTA('FactSampleInventory'[EquipmentID]), FILTER( ALLSELECTED(FactSampleInventory[CreateDate]), ISONORAFTER(FactSampleInventory[CreateDate], MAX(FactSampleInventory[CreateDate]), DESC) ), FactSampleInventory[Status] <> "Removed" )- Anonymous6 years agoNot applicable
Still no luck with it. It appears to give similar results to the first calc
- Anonymous6 years agoNot applicable
Any advice on this?