Calculating Time between two time in the same date

[CupidC]

Hi All

I have encountered an issue that I need assistance with. Here is the problem:

I would like to calculate the idle time between a salesperson serving customers.

For instance, let's consider John on 26/06/2023. He served two customers, the first from 10:15 am to 11:15 am, and then he started serving another customer at 11:30 am. I am uncertain about which DAX function to use in order to calculate the 15-minute idle time between the two customer interactions.

Initially, I thought about using the RANKX function, but I'm unsure about the subsequent steps. Any help would be greatly appreciated.

Thank you in advance for your assistance.

[ryan_mayu · Accepted solution]

@CupidC 

you can try this

Column = 
VAR _last=maxx(FILTER('Table','Table'[Sales Person]=EARLIER('Table'[Sales Person])&&'Table'[Date]=EARLIER('Table'[Date])&&'Table'[From]<EARLIER('Table'[From])),'Table'[To])
return if(ISBLANK(_last),blank(),DATEDIFF(_last,'Table'[From],MINUTE))

pls see the attachment below

[CupidC · Accepted solution]

Hi @ryan_mayu

Firstly, thank you so much for your help. You are truly amazing!

I have a follow-up question: Does the calculation have to be performed as a calculated column, or is there a way to write it as a measure?

Additionally, I attempted to substitute the formula using a Variable instead of "earlier," but I encountered some issues and it didn't work. Do you happen to know a solution to this?

Thank you once again for your incredible support.

Cupid 🙂

[ryan_mayu · Accepted solution]

@CupidC 

you can try this

Column = 
VAR _last=maxx(FILTER('Table','Table'[Sales Person]=EARLIER('Table'[Sales Person])&&'Table'[Date]=EARLIER('Table'[Date])&&'Table'[From]<EARLIER('Table'[From])),'Table'[To])
return if(ISBLANK(_last),blank(),DATEDIFF(_last,'Table'[From],MINUTE))

pls see the attachment below

[CupidC · Accepted solution]

Hi @ryan_mayu

Firstly, thank you so much for your help. You are truly amazing!

I have a follow-up question: Does the calculation have to be performed as a calculated column, or is there a way to write it as a measure?

Additionally, I attempted to substitute the formula using a Variable instead of "earlier," but I encountered some issues and it didn't work. Do you happen to know a solution to this?

Thank you once again for your incredible support.

Cupid 🙂

[ryan_mayu · Accepted solution]

@CupidC 

if you want to create a measure , you can try this

Measure = 
VAR _last=maxx(FILTER(all('Table'),'Table'[Sales Person]=max('Table'[Sales Person])&&'Table'[Date]=max('Table'[Date])&&'Table'[From]<max('Table'[From])),'Table'[To])
return DATEDIFF(_last,max('Table'[From]),MINUTE)+0

pls see the attachment below