Forum Discussion

areias_br's avatar
areias_br
Icon for Helper I rankHelper I
1 year ago
Solved

Personalized Collum based on the next row value

Hi, I have a table with the following colluns:   Date | Hour | OperationCode | ID   Whenever I have a change in a operation, a new line is created with the record of the hour that operation st...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    you can try this

     

    datehour = 'Table'[Date]+'Table'[Hour]

     

    Column =
    VAR _next=MINX(FILTER('Table','Table'[datehour]>EARLIER('Table'[datehour])),'Table'[datehour])
    var _hour=maxx(FILTER('Table','Table'[datehour]=_next),'Table'[Hour])
    return if (ISBLANK(_hour),blank(), if ('Table'[Hour]>_hour, DATEDIFF((date(2020,1,1)+'Table'[Hour]),(date(2020,1,2)+_hour),MINUTE),DATEDIFF((date(2020,1,1)+'Table'[Hour]),(date(2020,1,1)+_hour),MINUTE)))
     
     
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
1 year ago

could you pls provide some sample data and expected output?

  • areias_br's avatar
    areias_br
    Icon for Helper I rankHelper I
    1 year ago

    Sure!

    02/08/2024 | 13:50 | Navigation | 1
    02/08/2024 | 23:30 | Mooring | 2
    03/09/2024 | 01:00 |  Moored | 3

    I expect to get the personalized collum as the following

    02/08/2024 | 13:50 | Navigation | 1 | 580 minutes
    02/08/2024 | 23:30 | Mooring | 2 | 90 minutes
    03/09/2024 | 01:00 |  Moored | 3 | ...

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      1 year ago

      how you get 580 mins and 90 mins?

      • areias_br's avatar
        areias_br
        Icon for Helper I rankHelper I
        1 year ago

        02/08/2024 | 13:50 | Navigation | 1 | 580 minutes (23:30 - 13:50 = 09:40 => 09*60 + 40)
        02/08/2024 | 23:30 | Mooring | 2 | 15 minutes (23:45 - 23:30 = 15)
        02/08/2024 | 23:45 |  Moored | 3 | ...

        I've changed the last value because it gets a little bit more complicated when day changes.