Measure to Count Inventory Based On Two Distinct Dates

[jt1999]

Hello Everyone,

My goal is to write a measure that will count parts based on two different dates, to ultimately get a snapshot of inventory at any set time. Here is a very basic example of what I want to accomplish:

PartID	Date_1	Date_2
1234	1/4/2022	2/4/2022
5678	1/4/2022	2/4/2022
9101	1/4/2022	2/5/2022
2345	2/1/2022	2/5/2022
6543	2/1/2022	2/5/2022
4341	2/1/2022	2/6/2022
3267	2/1/2022
3267	2/2/2022
3267	2/3/2022
3267	2/4/2022

As you can see there are parts with distinct ID's  in the "PartID" column. Date_1, and Date_2 are when the part enters inventory, and leaves inventory, respsectfully. My goal is to be able to put in a slicer, that allows you to select a range of dates, and will tell you what the count of inventory is at any given time. 

For example, lets say the slicer date ranges selected are:

1/4/2022 to 2/3/2022 - The result should be 2

1/4/2022 to 2/4/2022 - The result should be 1

1/4/2022 to Current Day - The result should be 4, as the 4 parts without a second date are still in inventory. This would give you a live inventory calculation. 

Ultimately, the goal is to see goods movement in, and out at any given time period, to result in a net inventory. Maybe this would better be accomplished with 2 seperate measures, I am not sure. I hope i did a halfway decent job of what I am trying to accomplish. Any help is appreciated!

[jt1999]

Sashir,

This was correct! Except for one little part. You had the >= and <= in the wrong order. I was getting a blank result, and once I switched them, it began functioning perfectly! Thank you very much, this was a HUGE help. 

[Shishir22 · Accepted solution]

Hello @jt1999 ,

Create a standalone Date dimension table as DimDate not having any relationship with any other table.

Use date from DimDate in slicer.

Use below measure to count inventory -

Measure = 
Var _minDate= Min(DimDate[Date])
Var _maxDate= Max(DimDate[Date])
Return
COUNTX(FILTER(Data,Data[Date_1]<=_minDate && Data[Date_2]>=_maxDate),Data[PartID])