Forum Discussion

boa's avatar
boa
Icon for Helper I rankHelper I
4 years ago
Solved

Timedifference between diffrent records (taking in account the day and location)

Hello,

I wonder if this is possible.  I'd like to calculate the duration between 2 records based on the date and the location.

This is how the database looks like: 

 

So I like to know how much time there is between the appointments. The result should be something like this:

Sometimes people don't show up for their appointment. It would be awesome if it is also possible to calculate also the time between appointments not taking the status 'not showing up' in account. 

 

 

Thank you in advance!

Need Help

5 Replies

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

    boa 

    pls try this

    Column = 
VAR _last=maxx(FILTER('Table',year('Table'[Start])=year(EARLIER('Table'[Start]))&&'Table'[Start]<EARLIER('Table'[Start])&&'Table'[Location]=EARLIER('Table'[Location])),'Table'[End])
return if(ISBLANK(_last),_last,('Table'[Start]-_last)*24*60)

Column 2 = 
VAR _last=maxx(FILTER('Table',year('Table'[Start])=year(EARLIER('Table'[Start]))&&'Table'[Start]<EARLIER('Table'[Start])&&'Table'[Location]=EARLIER('Table'[Location])&&'Table'[Status]<>"not showing up"),'Table'[End])
return if(ISBLANK(_last)||'Table'[Status]="not showing up",blank(),('Table'[Start]-_last)*24*60)

     

    pls see the attachment below

    • boa's avatar
      boa
      Icon for Helper I rankHelper I
      4 years ago

      Hi Ryan

      Thanks for your suggestion. Most of the calculation are correct, but your expression don't take a new day in account. If I try your suggestion, I get this:

       

      The results in green are correct. The results in red should be 0 because it is the first appointment of that day (on that location) but I have no idea how to fix that...

       

       

  • boa's avatar
    boa
    Icon for Helper I rankHelper I
    4 years ago

    ryan_mayu Thank you very much for your help. The syntax in DAX works 🙂