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