Calculating days in stock for vehicles

What I am trying to achieve is to calculate retrospectively the number of days a vehicle has been in stock based on given filtered date

A very simplified version of my model looks as follows:

image

Not all dimensions and their attributes are included as well as the value columns on the fact table and I have only included what I deem relevant (I might be wrong to assume this)

Sample fact table data looks as follows:

image

I have written DAX measures to determine the date the vehicle has been purchased aswell as the date it has been sold. If the vehicle hasn’t been sold, it’s considered part of stock and then the calculation needs be up to today.

My measures as follows:

StockStartDate:=

MINX(
FILTER(
‘factInventoryMovement’,
‘factInventoryMovement’[MovementType] = “Purchase”
),
RELATED(‘dimDate’[Date])
)

StockEndDate:=

VAR OutOfStockDate =
MAXX(
FILTER(
‘factInventoryMovement’,
‘factInventoryMovement’[MovementType] = “Sale”
),
RELATED(‘dimDate’[Date])
)

RETURN IF(ISBLANK(OutOfStockDate), TODAY(), OutOfStockDate)

Days In Stock:= DATEDIFF([StockStartDate], [StockEndDate], DAY)

This works ok, when no filter has been applied via dimDate, but filtering on specific date does not work as intended. I understand that this is due to the relationship applying the filter context on the model, but I don’t know how to overcome this.

In essence the filter context needs to be removed on factInventoryMovement in order to get the Purchase and Sale date, but then use the date selected in order to retrospectively calculate the number of days up to that point.

I hope I am making sense. Any help will much appreciated.

Hi @DoubleDP

Based on my understanding of your questions, you can use below formula to calculate StockStartDate and StartEndDate irrespective of the Date selected in Slicer.

StockStartDate =
CALCULATE (MINX ( factInventoryMovement, RELATED ( dimDate[Date] ) ),
REMOVEFILTERS ( dimDate[Date] ),‘factInventoryMovement’[MovementType] = “Purchase”)

StockEndDate =
VAR OutOfStockDate =
CALCULATE (MAXX ( factInventoryMovement, RELATED ( dimDate[Date] ) ),
REMOVEFILTERS ( dimDate[Date] ),‘factInventoryMovement’[MovementType] = “Sale”)
RETURN
IF (ISBLANK ( OutOfStockDate ), TODAY (), OutOfStockDate )

// RemoveFilter is equivalent to All in above scenario just in case you don’t know.//

I couldn’t clearly understood the second requirement i.e. how you want to use the date selected in slicer. If you are selecting Single date in slicer, you can use below function to get the selected date and use in your queries

Selected_Date = SELECTEDVALUE(dimDate[Date])

If above doesn’t solves your problem, request you to please provide more clarity on how date is selected in Slicer i.e. Single or Multiple and how you want to use them.

Thanks
Ankit Jain