Forum Discussion
Dallas7890
Resolver I
4 years agoDisplay time over 24 hours in Power BI
Hello I'm having a little challenge in a simple table trying to calculate the time passed, for example from 22:00 to 2:00 in the morning. I have tried this code to calculate the Duration in M...
- 4 years ago
Thank you so much, Vijay. The formula worked perfectly
Vijay_A_Verma
Most Valuable Professional
4 years agoThe formula for End DateTime will need to be changed to
if [End]>=[Start] then [Date]&[End] else Date.AddDays([Date],1)&[End]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzIAIiNDJR0gx8rE1MrAQCHAF8izALJAHEdfpVgdiEojmEoDEytjmCRQnwGUB9QXGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Start", type time}, {"End", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartDateTime", each [Date]&[Start], type datetime),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDateTime", each if [End]>[Start] then [Date]&[End] else Date.AddDays([Date],1)&[End]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "TotalMinutes", each Duration.TotalMinutes([EndDateTime]-[StartDateTime]))
in
#"Added Custom2"
Dallas7890
Resolver I
4 years agoThank you so much, Vijay. The formula worked perfectly