Need DAX help with Inventory on Hand

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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]

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?

[Anonymous]

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 properly

[Anonymous]

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]

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]

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

[Anonymous]

@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]

Any advice on this?

[Anonymous]

@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]

Yes that is correct. Assume a fact table with thousands of rows with an EquipmentID, a date it was created (i.e. CreateDate), a StatusDate for when the status changes, and a status description (i.e. Active, Removed). So if we pick a date of June 1, 2019 and look in the fact table we should see several rows for different Equipment IDs with a CreateDate of Jun 1, 2019 meaning they were placed into inventory on that date. But what about the thousands of Equipment IDs that were placed into Inventory on earlier days. They are still in Inventory. For example, a piece of equipment with a CreateDate of Mar 14, 2018 with no status change is still in inventory on Jun 1, 2019. I would like to count ALL of the pieces of equipment in inventory for each day. Hopefully this makes sense.

[Wimverh]

Can you check the following file: 
SampleInventory.zip 

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