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

[boa]

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!

[boa]

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

[ryan_mayu]

you are welcome

[ryan_mayu]

@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]

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...

[ryan_mayu · Accepted solution]

@boa 

updated the DAX

pls see the attachment below