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 !

 

IDTime ReceivedActionExpected Time Difference O/P
12110/9/2017 6:00In 
12110/9/2017 6:30Out30
12110/9/2017 6:50In 
12110/9/2017 7:00Out10
12110/9/2017 7:10In 
12110/9/2017 7:30Out20
12110/9/2017 7:32In 
12110/9/2017 7:43Out11
12110/9/2017 7:47In 
12110/9/2017 7:50Out3

 

Thanks,

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")

15 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    Add this calculated Column

     

    =
VAR Previoustime =
    MAXX (
        FILTER ( Table1, Table1[Time Received] < EARLIER ( Table1[Time Received] ) ),
        Table1[Time Received]
    )
RETURN
    IF (
        Table1[Action] = "Out",
        DATEDIFF ( Previoustime, Table1[Time Received], MINUTE )
    )
    • erik_tarnvik's avatar
      erik_tarnvik
      Solution Specialist
      8 years ago

      Good solution from Zubair_Muhammad. You may want to check the Action in the filter condition in case times can overlap. Like this:

      =
VAR Previoustime =
    MAXX (
        FILTER ( Table1, Table1[Time Received] < EARLIER ( Table1[Time Received] ) &&
                               Table1[Action] = "In"),
        Table1[Time Received]
    )
RETURN
    IF (
        Table1[Action] = "Out",
        DATEDIFF ( Previoustime, Table1[Time Received], MINUTE )
    )

      You can also define the same as a measure. It would need to be executed in a filter context where Table1[Time Received] has only one value. It would go something like this:

      Diff = DATEDIFF(Calculate(MAX(Table1[Time Received]), 
                                FILTER(ALL(Table1), 
                                       Table1[Action] = "In" && 
                                       Table1[Time Received] <= MAX('Time'[Time Received]))), 
                      MAX('Time'[Time Received]), 
                      MINUTE)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        erik_tarnvik Zubair_Muhammad

         

         

        Here is the actual data, I tried to Include the Column and Measure, But I I am not getting the desired result.

        Am I doing anything wrong here

         

         

        Diff = DATEDIFF(Calculate(MAX('Events (3)'[Timestamp]),
        FILTER(ALL('Events (3)'),
        'Events (3)'[list.list.assetState.name] = "isNoMotion" &&
        'Events (3)'[Timestamp] <= MAX('Events (3)'[Timestamp]))),
        MAX('Events (3)'[Timestamp]),
        MINUTE)

         

         

         

        Column =
        VAR Previoustime =
        MAXX (
        FILTER ( 'Events (3)', 'Events (3)'[Timestamp] < EARLIER ( 'Events (3)'[Timestamp] ) &&
        'Events (3)'[list.list.assetState.name] = "isNoMotion"),
        'Events (3)'[Timestamp]
        )
        RETURN
        IF (
        'Events (3)'[list.list.assetState.name] = " isMotion",
        DATEDIFF ( Previoustime, 'Events (3)'[Timestamp], MINUTE )
        )

  • 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())

       

       

       

       

  • nickchobotar's avatar
    nickchobotar
    Skilled Sharer
    8 years ago

    Hello,

     

    Zubair_Muhammad  very elegant solution. 

     

    I took a longer path to the solution. Here is my take on this.

    EVALUATE
SELECTCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            Table1,
            "PreviousRow", CALCULATETABLE (
                VALUES ( Table1[Time Received] ),
                FILTER ( ALL ( Table1 ), Table1[Index] = EARLIER ( Table1[Index1] ) )
            )
        ),
        "Subtract", MINUTE ( ( Table1[Time Received] - [PreviousRow] ) )
    ),
    "Time Received", Table1[Time Received],
    "Action", Table1[Action],
    "Minutes Out", IF ( Table1[Action] = "Out", [Subtract], 0 )
)