Forum Discussion

alamhaz's avatar
alamhaz
Icon for Helper I rankHelper I
5 years ago
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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    5 years ago

    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

Mohammad_Refaei's avatar
Mohammad_Refaei
Icon for Solution Specialist rankSolution Specialist
5 years ago

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

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

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.