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