Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
alamhaz
Helper I
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
Mohammad_Refaei
Solution Specialist
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

View solution in original post

3 REPLIES 3
Mohammad_Refaei
Solution Specialist
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.

Jerry123__0-1654185314861.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.