Forum Discussion
Need DAX help with Inventory on Hand
I really need some help with a DAX calculation where I can see how many pieces of Inventory I have at a daily or monthly level. In my data model I have a FactInventory table that has EquipmentID, CreateDate, StatusDate, and Status (which has "Active", "Removed" and a couple other unimportant statuses) as well as some other attributes. I also have a custom Date table that has a relationship to the CreateDate in the Fact table.
What I would like to do is create a measure called Inventory on Hand that would show me, say on June 1 2019 as an example, how many pieces of inventory I have on that date (where the create date is before Jun 1, 2019 and the status has not changed to "Removed" before that date. Each day's inventory on hand should should roll up to the month level also (which is the level I really want to report on) to show for the month of June (and the other months) how many pieces of inventory i have on hand.
InventoryCount = COUNT('FactInventory'[EquipmentID]) gives me a count of inventory for each day but that is not taking into account all of the inventory still active from prior days that has not been removed.
Any help would be appreciated
Scott
12 Replies
Some sample data would be helpful, but the CALCULATE function would let you do this.
Try something along the lines of;
CALCULATE(COUNT('FactInventory'[EquipmentID]), [CreateDate] < whatever date you want, [Status] <> "Removed"))If you post some example data I can tailor this a little better to your use case.
Here is a sample PBIX file link Again I need a measure that will for ANY given date show how much inventory is on hand for that date by determining if CreateDate is less than that date and if it was not "Removed" prior to that date
Can you check the following file:
SampleInventory.zipI have the feeling you have a endless running total. You always add, but never remove records in your historic.
I think you need to a column with +1 and -1 and then you can calculate correct over time what's the value.
