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

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 

    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.

  • 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.