Compare date to previous rows in new column

[Vegonzalez]

Hello,

I am working on response data date/times and trying to calculate when a unit (each row has separate unit data) is dispatched before a unit already in service clears the call using the Dispatch At date/time and Clear At date/time from the previous row.  I want the difference in time, if there is an overlap, in seconds in the new column.

Thanks!Units Busy At Same Time

[Anonymous]

Hi @Vegonzalez ,

Thanks ryan_mayu  for the quick reply and solution. I have some other ideas to add:
You can use the DATEDIFF function to calculate the difference between two dates.

(1) Create an index column in Power Query.

(2) Create a column in Power BI Desktop.

Column = 
var _clear=CALCULATE(MAX('2024 Calls DS (5)'[Clear At]),FILTER('2024 Calls DS (5)','2024 Calls DS (5)'[Index]=EARLIER('2024 Calls DS (5)'[Index])+1))
RETURN IF(ISBLANK(_clear),0,IF('2024 Calls DS (5)'[Dispatch At]<_clear,DATEDIFF('2024 Calls DS (5)'[Dispatch At],_clear,SECOND),0))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[ryan_mayu]

cuold you pls provide some sample data(not the screenshot) and the expected output?

[Vegonzalez]

Excel File attached as a lot of the data is private.  Expected output in Unit Busy at Same Time column.

File at https://www.dropbox.com/scl/fi/r9cymlrjmatf6hia26vsk/Report-Example.xlsx?rlkey=8alkavwl1o0235gjp1n48...

Thanks!

[ryan_mayu · Accepted solution]

@Vegonzalez 

you can create an index column in PQ

then use DAX to create a column

Column =

var _clear=maxx(FILTER('2024 Calls DS (5)','2024 Calls DS (5)'[Index]=EARLIER('2024 Calls DS (5)'[Index])+1),'2024 Calls DS (5)'[Clear At])

return if(ISBLANK(_clear),0,if('2024 Calls DS (5)'[Dispatch At]<_clear,(_clear-'2024 Calls DS (5)'[Dispatch At])*86400,0))

pls see the attachment below

[Vegonzalez]

Thanks.  That solution worked great.

[ryan_mayu]

you are welcome

[sergej_og]

Hey @Vegonzalez ,
you can do a kind of approach in PQ or in DAX as well.
PQ: You can merge the data with itself. Doublicate the table.
I guess your data is already sorted properly.
If yes, you can provide an index to your data starting with 0.
Then (the dublicated table) provide an index starting with 1.
--> Merge the tables depending on index (it is a unique number). Make your calculation and remove useless columns.

DAX: Provide an index (maybe you already have a unique ID).
With this index you can use e.g. the WINDOW function.

Maybe you got the idea behind the post.

Regards.