Forum Discussion
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 (Stock On Hand) as of the last date within the Fact table. I would like to calculate the number of consecutive days that the Product in a store has had a Zero Stock on Hand value (SOH is a column in the fact table as is Date. I have been able to calculate how many days within a range have has zero stock, but I need to calculate the consecutive days. Example if product X in store A has a SOH value of 12 on the 13th, then zero on the 14th, 15th, 16th, the result should be 3 (14th-16th). I have no idea how to even start figuring this out.
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
3 Replies
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.
There is a Search Box on each of the pages that will look through all the contents...
Here's one of the many solutions that the Box has found for me within seconds:
Solved: Consecutive Days an event has occured - Microsoft Power BI Community
