How do I create a column that returns the change in time for two times in the same column?

[xsaldana]

I am attempting to create a column in Power BI that will return the difference in time. The table below is an example of how my data is currently formatted. For instance, the first value in the "Time Difference" will be 0 and the second will be 4 minutes (2:05:00 AM - 2:01:00 AM) and the second will be 1 minute (2:06:00 AM - 2:05:00 AM). Any suggestions on how to tackle this?

[tackytechtom · Accepted solution]

Hi @xsaldana ,

Here a possible solution 🙂

Here the DAX:

Time Difference = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    ),
    'Table'[Time],
    MINUTE
)

Does this solve your issue? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[xsaldana]

Hello! Yes, that worked great! Could you help me with this new issue that I encountered? I need the "TimeDifference" to be calculated again as previously discussed, but only if the location and date are the same. 

this is an example of the "TimeDifference" column I want to generate. Thank you so much! 

[tackytechtom]

Hi @xsaldana ,

How about this:

Here the code:

Time Difference = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table18'[Time] ),
        FILTER ( 'Table18', 'Table18'[Index] = EARLIER ( 'Table18'[Index] ) - 1 ) , 
        FILTER ( 'Table18', Table18[Location] = EARLIER ( 'Table18'[Location] ) ), 
        FILTER ( 'Table18', Table18[Date] = EARLIER ( 'Table18'[Date] ) )
    ),
    'Table18'[Time],
    MINUTE
)

Let me know if this is the solution for your problem 🙂

Thanks!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/