Forum Discussion

jayjay0306's avatar
jayjay0306
Icon for Helper III rankHelper III
1 year ago
Solved

DAX: skip row by criteria?

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

 

 

  • johnt75's avatar
    johnt75
    1 year ago

    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
    		
    )
    

10 Replies

  • 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 )
  • 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's avatar
      johnt75
      Icon for Super User rankSuper User

      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's avatar
      johnt75
      Icon for Super User rankSuper User

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

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User

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

      • jayjay0306's avatar
        jayjay0306
        Icon for Helper III rankHelper III

        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