Forum Discussion
Need DAX help with Inventory on Hand
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.OK cool, so it seems you just want to be able to change the date to which CreateDate is compared to. We could use parameters to create a flexible date field, that won't interact with / mess up your current data model.
Create three parameters (Modelling > New Parameter) one called day with values 1-31 with an increment of 1, one called month, values 1-12 and one called year, maybe 2000-3000.
Then create a measure that will generate your date;
Dynamic Date = Date = DATE(Parameter[Day],'Parameter 2'[Month],'Parameter 3'[Year])Then adjust the first piece of code I gave you to use that;
Inventory on Hand = CALCULATE(COUNT('FactInventory'[EquipmentID]), [CreateDate] < [Dynamic Date], [Status] <> "Removed"))Now you can use that measure wherever you like. If you set up three parameter controls then you can dynamically input the date you want to compare to. Hope that helps?
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
Inventory on Hand =CALCULATE (COUNT ( 'FactInventory'[EquipmentID] ),FactInventory[CreateDate] < DATE ( 2019, 6, 1 ),'Status'[Status] <> "Removed")As you can see the measure is not counting rows properlyOK 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?
