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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pak
Helper II
Helper II

Time difference between next row

Hi all,

 

I have another question,

 

A want to to count time difference between actual row and next row, when "bit" equals 1, like beneath:

Date/TimeBitTime Difference
2018-01-11T17:03:00110
2018-01-11T17:03:10110
2018-01-11T17:03:2014
2018-01-11T17:03:24110
2018-01-11T17:03:34110
2018-01-11T17:03:44110
2018-01-11T17:03:5416
2018-01-11T17:04:000 
2018-01-11T17:04:100 
2018-01-11T17:04:160 

 

There is no function for next row in table. I've tried different functions but always with error or 0 value in TimeDifference.

I also added two index column. The difference between them is: Index.1=Index + 1. Tried to use it but i have syntax error:

 

I made this using method from this tutorial:

https://www.youtube.com/watch?v=xN2IRXQ2CvI&app=desktop

 

But maybe there is another, better way?

What will happen when i delete column used to merge tables, when my data is live streamed from IoT Hub to Azure StreamAnalytics to TableStorage, to Power BI. Would it work for another rows after?

 

1 ACCEPTED SOLUTION

@pak

 

May be a MEASURE instead of a column would handle memory better.

Try this MEASURE

 

Time Difference Measure =
VAR NextRow =
    CALCULATE (
        MIN ( TableName[Date/Time] ),
        FILTER (
            ALL ( TableName ),
            TableName[Date/Time] > SELECTEDVALUE ( TableName[Date/Time] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( TableName[Bit] ) = 1,
        DATEDIFF ( SELECTEDVALUE ( TableName[Date/Time] ), NextRow, SECOND )
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

14 REPLIES 14
Greg_Deckler
Super User
Super User

Have a look at this article as it does something very similar. The trick is to use EARLIER. 

 

https://www.linkedin.com/pulse/mean-time-between-failure-mtbf-power-bi-greg-deckler-microsoft-mvp-/?...

 

You may need to use MAX instead of MIN, just depends on which direction you want to go.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @pak

 

If your Column is formatted as Date/Time, then this calculated column will get you the desired result

 

Time Difference =
VAR NextRow =
    CALCULATE (
        MIN ( TableName[Date/Time] ),
        FILTER (
            ALL ( TableName ),
            TableName[Date/Time] > EARLIER ( TableName[Date/Time] )
        )
    )
RETURN
    IF ( TableName[Bit] = 1, DATEDIFF ( TableName[Date/Time], NextRow, SECOND ) )

Regards
Zubair

Please try my custom visuals

@pak

 

9600.png


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad

 

Thanks,

Your method works well in small table, but in my case i have for now 22k rows and when i tried to do this i'm getting information:

"There's not enough memory to complete this operation. Please try again later when there may be more memory available."

 

 

Should it need so much power to calculate that?

@pak

 

May be a MEASURE instead of a column would handle memory better.

Try this MEASURE

 

Time Difference Measure =
VAR NextRow =
    CALCULATE (
        MIN ( TableName[Date/Time] ),
        FILTER (
            ALL ( TableName ),
            TableName[Date/Time] > SELECTEDVALUE ( TableName[Date/Time] )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( TableName[Bit] ) = 1,
        DATEDIFF ( SELECTEDVALUE ( TableName[Date/Time] ), NextRow, SECOND )
    )

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

Sadly i don't have any result with this method (all rows are null). I tried to mix it up but with no result.

@pak

 

It works with the sample data you shared

 

7501.png


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

So this is strange, cause in my case it doesn't:

td.jpg

 

The only difference: i have ";" and you have ",". With "," i get Syntax error.

 

@pak

 

You are adding it as a calculated column.

 

PLease add it as a MEASURE


Regards
Zubair

Please try my custom visuals

Thanks @Zubair_Muhammad  - for your time and patience 🙂

 

Rookie mistake! 🙂

 

It would be very helpfull for me in the future!

 

 

@pak

 

So did it work???... No memory issues??

 

Smiley Very Happy


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

With even 1000 of rows it is ok, but when i tried to do this with all data (for now it's over 30000 rows) it was using 100% of my memory for 10minutes with no result. I had to kill the process because i was unable to do anything on my PC.

 

Maybe it is because we take MIN from all rows from table with every row calculation?

 

Maybe i'll add INDEX column and next row will be INDEX+1 row, so there is no need to calucate all?

 

Also with Time Diference Measure i was unable to SUM all time difference, and i have to do so.

sukram
Regular Visitor

@pak 

 

Did your mehtod with INDEX work to reduce memory consumption? If yes, how exactly did you realize it?

 

Thank you very much

@pak

 

You can add MEASURE from the MODELLING tab or by right clicking the Table

 

Then use a Table Visual as shown in the Picture below

 

7502.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.