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

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?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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"
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Still no luck with it. It appears to give similar results to the first calc

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Any advice on this?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous - what results are you expecting? Perhaps you could mock up a table with your expected result as I'm a bit unsure what you are after.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

      Try this, if not correct, could you tell the expected result in the sample pbix.

       

      Inventory on Hand = 
CALCULATE (
COUNTROWS( 'Table' ),ALL('Table'),DATESMTD('Table'[CreateDate]),FILTER(ALL('Table'),
'Table'[Status] <> "Removed"))

       

       

       

      Paul Zheng _ Community Support Team
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.