cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

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

1 ACCEPTED SOLUTION
Solution Specialist

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 3
Solution Specialist

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.

Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors