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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi Anonymous

         

        Could you share the file?

        I noticed a small space in the code " isMotion" (3rd last line of the column code)
        Should be "isMotion"