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)
     
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)
 
  • villa1980's avatar
    villa1980
    Resolver II
    1 year ago

    wow, you have hit the nail on the head with this, works an absolute treat, thank-you so much.
    I have a couple of questions if you don't mind...

    1) If I wanted to do this for start date as a group, how would I add that?

    2) In simple speak could you explain what it does 🙂

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      1 year ago

      villa1980 

      what do you man by start date as a group? It is a table visual. Usually it will automatically group the column data which is text or date format.

       

      • villa1980's avatar
        villa1980
        Resolver II
        1 year ago

        That is fine, think I was just trying to over think it, I just need to highlight if is less than 60 and then calculate form that, is fine.

        Thank-you once again