Forum Discussion

Dallas7890's avatar
Dallas7890
Icon for Resolver I rankResolver I
4 years ago
Solved

Display 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...
Need Help
  • Dallas7890's avatar
    Dallas7890
    4 years ago

    Thank you so much, Vijay. The formula worked perfectly

Dallas7890's avatar
Dallas7890
Icon for Resolver I rankResolver I
4 years ago

Hi Vijay

 

I didn't have a Start Datetime or End Datetime column to work with. I had to try and create these columns. 

 

This is the only data 

That is the beginning of my problem. I didn't have a start date/time column or End date/time. I tried to create both columns using power query using the column merge with a space. Using the Date and Start and then the Date and End. This end column isn't correct for the ones that are past 12 am should go to the next day. 
 
 
If I could create a good End Date Time column, using the Hours but I didn't know how to do that. 
 
Would you know how I can do that?
 
Thank you kindly for your help
 
 
 
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

It looks like that your shift ends next day. You will need to create Start Datetime and End Datetime like these in PQ

=[Date]&[Start]

=Date.AddDays([Date],1)&[Start]

Then in PQ, you can use following formula for Total Minutes

= Duration.TotalMinutes([EndDateTime]-[StartDateTime])

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUNzIAIiNDJR0gx8rE1MrAQCHAF8izALJAHEdfpdhYAA==", 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 Date.AddDays([Date],1)&[End]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "TotalMinutes", each Duration.TotalMinutes([EndDateTime]-[StartDateTime]))
in
    #"Added Custom2"