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")
erik_tarnvik's avatar
erik_tarnvik
Solution Specialist
8 years ago

Hi Anonymous, I am not sure what is going wrong and I agree with Zubair_Muhammad that at this point, you probably need to share your model. I replicated your data exactly and got the following result using my Diff measure:

Since the DATEDIFF function uses MINUTE as the resolution, most of the results are zero minutes, but the first two lines in your data should give a Diff of 8, which it also does in my testing. I can't see anything obviously wrong in your replaication of the Diff measure.

 

Just to verify, I changed the resolution to SECOND, which gave an output that also seems correct. Well, except for the total but that's a whole different story...

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi erik_tarnvik

I am looking for the Exact result that you have shown, But When I tried it for Seconds for some reason I am not getting accurate results(as you can see seconds are not correct).Here is the exact data used given in the Image, I am unable to upload files in here:(

 

 

 

 

 

 

 

Timestamplist.list.assetState.name
9/27/2017 10:11isMotion
9/27/2017 10:12isNoMotion
9/27/2017 10:15isMotion
9/27/2017 10:16isNoMotion
9/27/2017 10:19isMotion
9/27/2017 10:24isNoMotion
9/27/2017 10:24isMotion
9/27/2017 10:31isNoMotion
9/27/2017 10:31isMotion
9/27/2017 10:38isNoMotion
9/27/2017 10:39isMotion
9/27/2017 10:42isNoMotion
9/27/2017 10:42isMotion
9/27/2017 10:44isNoMotion
9/27/2017 10:44isMotion
  • erik_tarnvik's avatar
    erik_tarnvik
    Solution Specialist
    8 years ago

    Try changing the line

    FILTER(ALL('Events (3)'),

    to

    FILTER(ALLSELECTED('Events (3)'),

    in your measure. What happens here is that the ALL() clears all filters on your event table no matter where they are coming from. ALLSELECTED however will clear the filters in your table but not the one in the slicer I see you have on the events table. So you most likely are getting the wrong result beacuse the measure will "see" ALL events in the table, not just the ones you have selected with the slicer.

     

    Let me know if this solves the problem.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi erik_tarnvik

      Thanks, I am getting the result now. But there is just one more thing, Is it Possible to get the exact time difference with Minutes and seconds(m.s) for eg. 4.50 where 4 is minute and 50 is seconds both of which is derived from the difference in date times.

       

      Thanks,

      Ravi Shankar D

      • erik_tarnvik's avatar
        erik_tarnvik
        Solution Specialist
        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")