Forum Discussion

alamhaz's avatar
alamhaz
Helper 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