Forum Discussion

Leslie1015's avatar
Leslie1015
Frequent Visitor
3 years ago
Solved

How to Calculate Time difference from a single column

Hello guys,

 

I'm new to power BI and I really need help in getting the time difference from a single column,

I have the Date and and Index. I needed an output like this. Can someone help me

   Time Difference
DateIndex  
Wed, 01 Mar 2023 07:00:53     0  
Wed, 01 Mar 2023 07:01:12     1 0:00:19
Wed, 01 Mar 2023 07:01:23     2 0:00:11
Wed, 01 Mar 2023 07:01:30     3 0:00:07
Wed, 01 Mar 2023 07:07:30     4 0:06:00
Wed, 01 Mar 2023 07:09:30     5 0:02:00
  • In Power Query (Transform Data), you can

    • you don't need the Index column
    • if Date column not sorted ascending, then do so
    • Add a "shifted date column" where the dates are shifted down one
    • Subtract the "shifted column" from the date column
    • Remove the "shifted column"

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9N0VEwMFTwTSxSMDIwMlYwMLcyMLAyNVaK1cEha2hlaIRP1givXmMD3LLmeGUtwbKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
        
        #"Add Shifted Date Column" = 
            Table.FromColumns(
                Table.ToColumns(#"Changed Type") & 
                {{null} & List.RemoveLastN(#"Changed Type"[Date],1)},
                type table[Date=datetime, Shifted Date=nullable datetime]),
        
        #"Add Time Difference Column" = Table.AddColumn(#"Add Shifted Date Column", "Time Difference", 
                each [Date] - [Shifted Date], type duration),
                
        #"Remove Shifted Date Column" = Table.RemoveColumns(#"Add Time Difference Column",{"Shifted Date"})
    in
        #"Remove Shifted Date Column"

     

     

     

    You could also do this with DAX, using a similar algorithm.

    Again, the below assumes the DATE column is sorted ascending.

     

    You select to add a column, with this DAX formula:

    Time Diff = var SHIFT = OFFSET(-1,ALL('Table'[Date]))
                return if(SHIFT=0,BLANK(),'Table'[Date] - SHIFT)

     

     

2 Replies

  • In Power Query (Transform Data), you can

    • you don't need the Index column
    • if Date column not sorted ascending, then do so
    • Add a "shifted date column" where the dates are shifted down one
    • Subtract the "shifted column" from the date column
    • Remove the "shifted column"

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9N0VEwMFTwTSxSMDIwMlYwMLcyMLAyNVaK1cEha2hlaIRP1givXmMD3LLmeGUtwbKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
        
        #"Add Shifted Date Column" = 
            Table.FromColumns(
                Table.ToColumns(#"Changed Type") & 
                {{null} & List.RemoveLastN(#"Changed Type"[Date],1)},
                type table[Date=datetime, Shifted Date=nullable datetime]),
        
        #"Add Time Difference Column" = Table.AddColumn(#"Add Shifted Date Column", "Time Difference", 
                each [Date] - [Shifted Date], type duration),
                
        #"Remove Shifted Date Column" = Table.RemoveColumns(#"Add Time Difference Column",{"Shifted Date"})
    in
        #"Remove Shifted Date Column"

     

     

     

    You could also do this with DAX, using a similar algorithm.

    Again, the below assumes the DATE column is sorted ascending.

     

    You select to add a column, with this DAX formula:

    Time Diff = var SHIFT = OFFSET(-1,ALL('Table'[Date]))
                return if(SHIFT=0,BLANK(),'Table'[Date] - SHIFT)

     

     

  • From what I understand, you want the time difference from the previous row.

    It's not going to be pretty but it should work:

    1) Looks like you have already done a SORT BY on Date and added the Index column. Good keep those, you are gong to need them.

    2) DUPLICATE this query. Add a Custom Column named [New Index] and make it equal [index] - 1.

    3) MERGE the two queries. Join on [Index] from the original table and [New Index] of the new table.

    4) Expand the new table and grab only the Date.

    You should now have one date/time and the previous date/time on one row. 

    Do the match between the two.

     

    FYI: If this is SQL, you should investigate WINDOW functions, particularly LAG and LEAD which allow you to find the row X number of rows behind (LAG) or x number of rows ahead (LEAD) of the current rwo. Plus anytime you can do something in SQL instead of Power Query, do it.