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