Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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, Cre...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?