Forum Discussion

JWPowder's avatar
JWPowder
Frequent Visitor
4 years ago
Solved

Find Next Start Time

Hello, I have a table of data that has start times listed in a column next to a machine identifyer (title) column. 

 

I am using this DAX function to find the next time that a status was changed on this machine. 

 

 

Next Start Time = IF(statusboardpunches2[Title] = LOOKUPVALUE(statusboardpunches2[Title],statusboardpunches2[Index2],statusboardpunches2[Index2]+1),LOOKUPVALUE(statusboardpunches2[Time],statusboardpunches2[Index2],statusboardpunches2[Index2]+1),BLANK())

 

However, it is not returning values for each row:

 

 

As you can see several rows are unpopulated and I cannot figure out what I am missing. 

 

Ideally, each row would have a end time, which is just the start time from the next update. 

 

Thank you in advance. 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, JWPowder 

     

    You can try the following methods.

    Column:

    Rank = 
RANKX ( FILTER ( 'Table', [Title] = EARLIER ( 'Table'[Title] ) ), [Time],, ASC )
    Next Start Time = 
CALCULATE (
    MAX ( 'Table'[Time] ),
    FILTER (
        'Table',
        [Rank]
            = EARLIER ( 'Table'[Rank] ) + 1
            && [Title] = EARLIER ( 'Table'[Title] )
    )
)

    This matches the output you expect.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    JWPowder , Logic is not very clear

     

    an ew column = Var _next = Minx(filter(Table, [Title] =earlier([Title]) && [Time] > earlier([Time])), [Time])
    var _status = Minx(filter(Table, [Title] =earlier([Title]) && [Time] =_next), [status])
    return
    if(_status <> [Status], _next, blank())

     


    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • JWPowder's avatar
      JWPowder
      Frequent Visitor
      4 years ago

      Hello, thank you for your response. 

       

      Sample Data:

      TitleTimeOperatorIndex
      ACM 30-25/17/2022 10:00 AMJW

      0

      EXT 245/17/2022 11:00 AMJC

      1

      ACM 40-65/17/2022 11:30 AMCJD2
      ACM 30-25/17/2022 12:00 PMJW3
      ACM 40-65/17/2022 12:15 PMJC4
      EXT 245/17/2022 12:45 PM 5

       

      Desired Output:

      TitleTimeOperatorIndexNext Start Time
      ACM 30-25/17/2022 10:00 AMJW0 5/17/2022 12:00 PM
      EXT 245/17/2022 11:00 AMJC15/17/2022 12:45 PM
      ACM 40-65/17/2022 11:30 AMCJD25/17/2022 12:15 PM
      ACM 30-25/17/2022 12:00 PMJW35/17/2022 1:00 PM
      ACM 40-65/17/2022 12:15 PMCJD45/17/2022 2:30 PM
      EXT 245/17/2022 12:45 PM JC55/17/2022 3:30 PM

       

      Hopefully that helps. 

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        Hi, JWPowder 

         

        You can try the following methods.

        Column:

        Rank = 
RANKX ( FILTER ( 'Table', [Title] = EARLIER ( 'Table'[Title] ) ), [Time],, ASC )
        Next Start Time = 
CALCULATE (
    MAX ( 'Table'[Time] ),
    FILTER (
        'Table',
        [Rank]
            = EARLIER ( 'Table'[Rank] ) + 1
            && [Title] = EARLIER ( 'Table'[Title] )
    )
)

        This matches the output you expect.

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.