cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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" )
)``````

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" )
)``````

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 - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors