Forum Discussion
Solved
Calculating consecutive dates
Hi there, I don't even know how to start with this measure. I have a simple dataset, (Fact, Stores, Products, Calendar). I have created a report page those shows products by Store that have Zero SOH ...
You may create a measure as follows:
InventoryRupturePeriod = VAR LastDateofInventoryAvailability = CALCULATE ( MAX ( Stores[Date] ), FILTER ( ALLEXCEPT ( Stores, Stores[Store], Stores[Product] ), Stores[SOH] > 0 ) ) VAR StartingDate = CALCULATE ( MIN ( Stores[Date] ), FILTER ( ALLEXCEPT ( Stores, Stores[Store], Stores[Product] ), Stores[SOH] = 0 && Stores[Date] > LastDateofInventoryAvailability ) ) VAR EndDate = CALCULATE ( MAX ( Stores[Date] ), FILTER ( ALLEXCEPT ( Stores, Stores[Store], Stores[Product] ), Stores[SOH] = 0 && Stores[Date] > LastDateofInventoryAvailability ) ) RETURN COMBINEVALUES ( " | ", FORMAT ( StartingDate, "DD-MMM-YY" ), FORMAT ( EndDate, "DD-MMM-YY" ) )Please check this sample file
You may create a measure as follows:
InventoryRupturePeriod =
VAR LastDateofInventoryAvailability =
CALCULATE (
MAX ( Stores[Date] ),
FILTER ( ALLEXCEPT ( Stores, Stores[Store], Stores[Product] ), Stores[SOH] > 0 )
)
VAR StartingDate =
CALCULATE (
MIN ( Stores[Date] ),
FILTER (
ALLEXCEPT ( Stores, Stores[Store], Stores[Product] ),
Stores[SOH] = 0
&& Stores[Date] > LastDateofInventoryAvailability
)
)
VAR EndDate =
CALCULATE (
MAX ( Stores[Date] ),
FILTER (
ALLEXCEPT ( Stores, Stores[Store], Stores[Product] ),
Stores[SOH] = 0
&& Stores[Date] > LastDateofInventoryAvailability
)
)
RETURN
COMBINEVALUES (
" | ",
FORMAT ( StartingDate, "DD-MMM-YY" ),
FORMAT ( EndDate, "DD-MMM-YY" )
)
Please check this sample file
Try to apply the solution, proposed for a problem that I have in specific however the measures do not work correctly when there is more than one period of shortage.
As the measure could be modified to identify each of the periods with shortages, what I see is that it identifies the last one nothing more.
