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]

hi johnt75,

not quite.

please, let me give you an example:

Example- ”Product 1”

(a) ”Product 1” is registered as sold out (”empty”) on

     1/7/2025 18:57

(b) The shelf is refilled at 1/10/2025 6:57, BUT  “Product 1” is sold out again at 1/10/2025 7:04, which is less than 15 minutes after it was put on the shelf.

When this situation happens, I say it is redundant, and the sold out-period is extended to the NEXT “Refill”-time (c): 1/14/2025 17:57 .   

Therefore, the end result I am looking for is the following:

And, in fact, due to the redundance, the end result should be:

….but let us take one step at the time. It will be greatly appreciated, if you could give me feedback on the first part.

does it make sense?

br, 

Jakob

[johnt75 · Accepted solution]

That's a bit more complicated, but you can use

Next refill time = 
IF( SELECTEDVALUE( 'Table'[Event] ) = "Empty" ,
	VAR CurrentCupboard = SELECTEDVALUE( 'Table'[Cupboard_ID] )
	VAR CurrentProduct = SELECTEDVALUE( 'Table'[Product] )
	VAR CurrentDate = SELECTEDVALUE( 'Table'[DateTime] )
	VAR AllEmpties = CALCULATETABLE(
		VALUES( 'Table'[DateTime] ),
		TREATAS( { ( CurrentCupboard, CurrentProduct, "Empty" ) }, 'Table'[Cupboard_ID], 'Table'[Product], 'Table'[Event] ),
		REMOVEFILTERS( 'Table' )
	)
	VAR AllRefills = ADDCOLUMNS( 
		CALCULATETABLE(
			VALUES( 'Table'[DateTime] ),
			TREATAS( { ( CurrentCupboard, "Refill" ) }, 'Table'[Cupboard_ID], 'Table'[Event] ),
			REMOVEFILTERS( 'Table' )
		),
		"@num early empties",
		VAR StartDateTime = 'Table'[DateTime]
		VAR EndDateTime = StartDateTime + TIME( 0, 15, 0)
		VAR Result = COUNTROWS( FILTER( AllEmpties, 'Table'[DateTime] >= StartDateTime && 'Table'[DateTime] <= EndDateTime ) )
		RETURN Result
	)
	VAR NextValidRefresh = MINX( FILTER( AllRefills, ISBLANK( [@num early empties] ) && 'Table'[DateTime] > CurrentDate ), 'Table'[DateTime] )
	RETURN NextValidRefresh
		
)

[jayjay0306]

Brilliant. It is exactly what I nedded. thanks Johnt75. I will try to make the redundant row blank, and if you have an advice which sends me in the right direction, it will be greatly appreciated. 

But for now: Big thanks. 🙂

br,

jayjay0306

[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 )