Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

calculating duration between two cells based on matching criteria

Bit of a complex one...   I have a time/date column called [Date Created] and a [Username] column, I want to calculate the duration between the first and next times - when the [Username] is the sam...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    is this what you want?

    New Start Time = MINX(FILTER('Table','Table'[Username ]=EARLIER('Table'[Username ])&&'Table'[ Weekday ]=EARLIER('Table'[ Weekday ])&&'Table'[Date Created]>EARLIER('Table'[Date Created])),'Table'[Date Created])

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

Anonymous 

could you pls provide some sample data and expected output?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    ryan_mayu 

     

    The expected result would look something like the below. But what's happening at the moment is inconsistent where the blanks are or should be. 

     

    Date Created                  Username     Weekday      Index        New Start Time

    03/01/2020  09:49:26     Abs                6                  1               03/01/2020 10:07:05

    03/01/2020  10:07:05     Abs                6                  2               03/01/2020 10:35:22    

    03/01/2020  10:35:22     Abs                6                  3                 

    03/01/2020  16:10:51    Tod                 6                  4               03/01/2020 16:16:47

    03/01/2020  16:16:47    Tod                 6                  5                

    06/01/2020  09:50:30    Tod                2                   6               06/01/2020 10:16:23

    06/01/2020 10:16:23    Tod                 2                  7               06/01/2020 10:27:15

    06/01/2020 10:27:15    Tod                 2                  8               

    06/01/2020 10:37:13    Del                 2                  9               06/01/2020 10:58:21

    06/01/2020 10:58:21    Del                 2                  10               

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      5 years ago

      Anonymous 

      is this what you want?

      New Start Time = MINX(FILTER('Table','Table'[Username ]=EARLIER('Table'[Username ])&&'Table'[ Weekday ]=EARLIER('Table'[ Weekday ])&&'Table'[Date Created]>EARLIER('Table'[Date Created])),'Table'[Date Created])