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 c...
  • ryan_mayu's avatar
    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