Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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,

1 ACCEPTED SOLUTION

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

View solution in original post

15 REPLIES 15
nickchobotar
Skilled Sharer
Skilled Sharer

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

image.png

 

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

I tried using the formula, but it gives a difference in Date's

Capture.JPGcol = 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())

 

 

 

 

Hi,

 

Format that column as a Time entry.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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
Not applicable

@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

Capture.JPG

 

 

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, 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:

image.png

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...

 

image.png

 

Anonymous
Not applicable

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,

Anonymous
Not applicable

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:(

 

 

Capture.JPG

 

 

 

 

 

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

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
Not applicable

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

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")
Anonymous
Not applicable

Thanks a lot @erik_tarnvik and everyone who helped in forming this calculation.

I will check and let you know if I find any bugs @erik_tarnvik

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"



Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.