Forum Discussion
Leslie1015
3 years agoFrequent Visitor
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 ...
- 3 years ago
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)
ronrsnfld
3 years agoSuper User
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)