Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago
Solved

Time Duration between rows

I am trying to look at the duration between the end date and next start date for each STARTDATE category. I am using the below DAX and is coming out with some weird results which I am struggling to ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    villa1980 

    since you have the index column , you can try this

     

    Measure =
    var _end=maxx(FILTER(all('Table'),'Table'[BAYDESC]=max('Table'[BAYDESC])&&'Table'[Index]=max('Table'[Index])-1),'Table'[ENDDATE])
    return DATEDIFF(_end,max('Table'[STARTDATE]),MINUTE)
     
ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

villa1980 

all your end date is the same as the next start date. What's the expected output based on the screenshot you provided?

villa1980's avatar
villa1980
Resolver II
1 year ago

Hi Ryan,
 There aren't although there are not many you can see on the 4th the end date is 15:00 and the next start date is 15:30, I need this to be flagged for another calculation so that is why I am trying to return the gap duration

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    villa1980 

    is this what you want?

     

    Measure =
    VAR _end=maxx(FILTER(ALL('Table'),'Table'[CENTERID]=max('Table'[CENTERID])&&'Table'[ENDDATE]<max('Table'[ENDDATE])),'Table'[ENDDATE])
    return DATEDIFF(_end,max('Table'[STARTDATE]),MINUTE)
     
     
    pls see the attachment below
    • villa1980's avatar
      villa1980
      Resolver II
      1 year ago

      ooh getting closer, these is what it returns. Not sure why it has -30 on the other rows
      I did attach a link to the pbix, maybe it is that?

       

       

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        1 year ago

        villa1980 

        since you have the index column , you can try this

         

        Measure =
        var _end=maxx(FILTER(all('Table'),'Table'[BAYDESC]=max('Table'[BAYDESC])&&'Table'[Index]=max('Table'[Index])-1),'Table'[ENDDATE])
        return DATEDIFF(_end,max('Table'[STARTDATE]),MINUTE)