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

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

The shift can't end before the shift starts. This issue will lead to data inconsistency.

Assuming the intention to put this data is correct, then you can use below formula

=ABS([Shift start Date]-[Shift end Date])*1440
Dallas7890's avatar
Dallas7890
Icon for Resolver I rankResolver I
4 years ago

Hi Vijay,

 

This formula works perfectly except for some really do end on the same day

 

Is there any way to tell the formula that?

 

Here is what it looks like now using your amazing formula

The only problem if you see some are really starting at 6:30 am and ending on the same day shift at 4:30 pm

 

Thank again for helping me with this.

Much appreciated. 

 

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    The 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's avatar
      Dallas7890
      Icon for Resolver I rankResolver I
      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

    Here is a better look. For example, some really do start and end on the same shift 

     

    for example the start at 7:00:00 AM should still end on the same day 4/1/2021 at 8:00:00pm

     

    also the 12:00 AM to 12:00: AM should all just remain as the same day

    No time was really worked on these days

     

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      Replacing End Date time with this formula will solve the problem

      if [End]>=[Start] then [Date]&[End] else Date.AddDays([Date],1)&[End]