DAX: skip row by criteria?

[jayjay0306]

Hi experts,

I have a DAX challenge which is driving me crazy and I hope I can explain this in a understandable way:

I have this table showing the time flow for refilling of products which has been sold out ("Empty") in a cupboard.

Each timestamp is registered with either:

Event="Refill" (=>time when the cupboard was filled again with new products)

Event="Empty" (=>time when the last product in the cupboard was sold)

I want to make two measures:

1) A measure "Next_Refill_Time", which  - for each product - calculates the time between Event="Empty" and the first subsequent Event="Refill" (ref.1 in illustration below).

BUT......(and here comes the tricky part).......IF the time between Event="Refill" and the first subsequent Event="Empty" IS LESS than 15 minutes, I need the calculation to jump to the next subsequent Event="Refill"? (ref.2 below)

2) a measure "Time_to_next_Refill(hours), which calculates the hours spent from a product is sold out ("empty") to the cuboard is "refilled"

Example:

I have this illustration, which tries to visualize the logic (please see the .pbix file attached)

In my attempt to make the measures mentioned, I have made the following:

Measure: "Next_Refill_Time"

Next_Refill_Time_ = 
VAR SoldOutEndTime = SELECTEDVALUE('Table'[DateTime])
VAR FirstRefillTime =
    CALCULATE(
        MIN('Table'[DateTime]),
        ALL('Table'),
        'Table'[Event] = "ReFill",
        'Table'[Cupboard_ID] = SELECTEDVALUE('Table'[Cupboard_ID]),
        'Table'[DateTime] > SoldOutEndTime
    )
VAR JumpValue=IF(DATEDIFF(SoldOutEndTime,FirstRefillTime,MINUTE) < ABS(15),BLANK(),FirstRefillTime)
RETURN
JumpValue

Measure: "Time_to_next_Refill(hours)

Total_SoldOut_Hours_ = 
SUMX(
    FILTER(
        'Table',
        'Table'[Event] = "Empty" &&
        (
            NOT NOT(ISBLANK(SELECTEDVALUE('Table'[Cupboard_ID]))) || 
            'Table'[Cupboard_ID] = SELECTEDVALUE('Table'[Cupboard_ID])
        ) &&
        (
            NOT NOT(ISBLANK(SELECTEDVALUE('Table'[Product]))) || 
            'Table'[Product] = SELECTEDVALUE('Table'[Product])
        )
    ),
    VAR SoldOutEndTime = 'Table'[DateTime]

    // "FirstRefillTime": The earliest "ReFill" event that occurs after SoldOutEndTime for the same Back_Stock_Unit_ID.
    
    VAR FirstRefillTime =
        CALCULATE(
            MIN('Table'[DateTime]),
            ALL('Table'),
            'Table'[Event] = "ReFill",
            'Table'[Cupboard_ID] = EARLIER('Table'[Cupboard_ID]),
            'Table'[DateTime] > SoldOutEndTime
        )

    // NextSoldOutTime: The earliest "SoldOut" event occurring within 15 minutes after FirstRefillTime for the same Back_Stock_Unit_ID
    
    VAR NextSoldOutTime =
        CALCULATE(
            MIN('Table'[DateTime]),
            ALL('Table'),
            'Table'[Event] = "Empty",
            'Table'[Cupboard_ID] = EARLIER('Table'[Cupboard_ID]),
            'Table'[DateTime] > FirstRefillTime,
            'Table'[DateTime] <= FirstRefillTime + TIME(0, 15, 0)
        )
    VAR IsValidPeriod = ISBLANK(NextSoldOutTime)
    VAR ValidRefillTime =
        IF(ISBLANK(FirstRefillTime), NOW(), FirstRefillTime)
    VAR SoldOutDuration =
        IF(
            IsValidPeriod,
            DATEDIFF(SoldOutEndTime, ValidRefillTime, MINUTE) / 60.0,
            0
        )
    RETURN SoldOutDuration
    )

Which gives this result:

ad.1 ) as you can see the part of the calculation, which finds the "next refill"-date/time appears to work. But for some reason, I can't get the "<15 min"-criteria to work?

ad.2) again, the hour-calculation appears to work between the "Empty" and the "Refill" date/time. But here the "<15 min"-criteria doesn't work either?

Please, If anyone can help me to get closer to the solution I need, It will be greatly appreciated.

I have the .pbix model to share if needed ( apparently, I can't attach the model here)

And if my explanation is insufficient, please let me know?

Thanks.

Br,

jayjay0306

[jayjay0306]

yes, can you access this?

model:

DAX Challenge - Next refill time.pbix

source:

Table.xlsx

[johnt75]

I don't see any rows where the next refresh is within 15 minutes, so everything appears to be working correctly.

[jayjay0306]

no, same result. Sorry.

br,

Jayjay0306

[johnt75]

can you share some data? You can upload to OneDrive, Google Drive or similar.

[jayjay0306]

Hi Johnt75,

thanks, but apparently I get the same result:

Next_Refill_Time_ = 
VAR SoldOutEndTime = SELECTEDVALUE('Table'[DateTime]) + TIME(0,15,0)
VAR FirstRefillTime =
    CALCULATE(
        MIN('Table'[DateTime]),
        ALL('Table'),
        'Table'[Event] = "ReFill",
        'Table'[Cupboard_ID] = SELECTEDVALUE('Table'[Cupboard_ID]),
        'Table'[DateTime] > SoldOutEndTime
    )
VAR JumpValue=IF(DATEDIFF(SoldOutEndTime,FirstRefillTime,MINUTE) < ABS(15),BLANK(),FirstRefillTime)
RETURN
JumpValue

[johnt75]

Try

Next_Refill_Time_ =
VAR SoldOutEndTime =
    SELECTEDVALUE ( 'Table'[DateTime] ) + TIME ( 0, 15, 0 )
VAR FirstRefillTime =
    CALCULATE (
        MIN ( 'Table'[DateTime] ),
        ALLEXCEPT ( 'Table', 'Table'[Cupboard_ID] ),
        'Table'[Event] = "ReFill",
        'Table'[DateTime] > SoldOutEndTime
    )
RETURN
    FirstRefillTime

[johnt75]

I think you can get what you're after by manipulating the sold out start time. If you add 15 minutes to that then that will ignore those refills which happened within that window.

e.g.

VAR SoldOutEndTime = SELECTEDVALUE('Table'[DateTime]) + TIME( 0, 15, 0 )

VAR SoldOutEndTime = 'Table'[DateTime] + TIME( 0, 15, 0 )