Forum Discussion

CupidC's avatar
CupidC
Helper II
3 years ago
Solved

Calculating Time between two time in the same date

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.

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    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's avatar
    CupidC
    3 years ago

    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's avatar
    ryan_mayu
    3 years ago

    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

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    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's avatar
      CupidC
      Helper II
      3 years ago

      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's avatar
        ryan_mayu
        Super User
        3 years ago

        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