Forum Discussion
Help with Measure
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")
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)
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 )
)
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"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...
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:(
Timestamp list.list.assetState.name 9/27/2017 10:11 isMotion 9/27/2017 10:12 isNoMotion 9/27/2017 10:15 isMotion 9/27/2017 10:16 isNoMotion 9/27/2017 10:19 isMotion 9/27/2017 10:24 isNoMotion 9/27/2017 10:24 isMotion 9/27/2017 10:31 isNoMotion 9/27/2017 10:31 isMotion 9/27/2017 10:38 isNoMotion 9/27/2017 10:39 isMotion 9/27/2017 10:42 isNoMotion 9/27/2017 10:42 isMotion 9/27/2017 10:44 isNoMotion 9/27/2017 10:44 isMotion 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.
Hi erik_tarnvik
Need one more help from you, the Diff measure is giving me the time difference perfectly but is it possible to get the total also. I also need to show the total time difference. Can you help me with this.
Thanks,
