How to find second difference between two dates in the same column

[Anonymous]

I'm trying to replicate in powerbi what I have in the excel screenshot. I want to find the time difference between two adjacent dates and output the time difference in seconds to the next column.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You could try to add an index column and use the function of DATEDIFF to calculate. I created a calculated column to implement it that you can have a try.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xueding\Desktop\DateTime.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DateTime", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

Column = var last = CALCULATE(MAX('Table'[DateTime]),FILTER('Table','Table'[Index] =EARLIER( 'Table'[Index]) -1))
return
DATEDIFF(last,MAX('Table'[DateTime]),SECOND)

Best Regards,

Xue Ding

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

[kentyler]

I think the standard advice for doing calculations involving adjacent cells is to do it in Excel....Excel is designed for it, Power BI is not. Then load your data into Power BI to use its features for analysis.