Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Help with Measure

Hi, I need help from you guys for calculating the difference between two time stamps , below is as example of the exact output that I need. Appreciate your help !   ID Time Received Action ...
Need Help
  • erik_tarnvik's avatar
    erik_tarnvik
    8 years ago

    Hi Anonymous,

    glad it worked out for you. For formatting the result, you may find these additional measures useful:

    DiffMinutes = QUOTIENT([Diff], 60)

DiffSeconds = [Diff] - [DiffMinutes] * 60

DiffMinSec = [DiffMinutes] & "." & FORMAT([DiffSeconds],"00")

    If all you need is the [DiffMinSec] you can fold it together as:

    DiffMinSec = QUOTIENT([Diff], 60) & "." & FORMAT([Diff] - QUOTIENT([Diff], 60) * 60,"00")
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi Anonymous,

 

Try this calculated column formula

 

=if(Data[Action]="Out",[Time Received]-CALCULATE(MAX(Data[Time Received]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Time Received]<EARLIER(Data[Time Received]))),BLANK())

Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Ashish_Mathur

I tried using the formula, but it gives a difference in Date's

col = if('Events (3)'[list.list.assetState.name]="isNoMotion",'Events (3)'[Timestamp]-CALCULATE(MAX('Events (3)'[Timestamp]),FILTER('Events (3)','Events (3)'[Timestamp]<EARLIER('Events (3)'[Timestamp]))),BLANK())

 

 

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Format that column as a Time entry.