How to add TIME values in DAX or Power Query

[smjzahid]

I have a colum of type TIME datatype and I want to sum the values in this column to find out TOTAL HOURS or TOTAL MINUTES WORKED.

Doing this in excel is really straight forward, but I cannot do this in Power BI Desktop using (DAX or Power Query)

How do I do this in DAX or Power Query, 

[FrankG]

Hi, 

Maybe there are more elgant solutions, but I think the below will work:

#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Time.ToRecord(Time.From([Time]/86400))),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Hour", "Minute", "Second"}, {"Custom.Hour", "Custom.Minute", "Custom.Second"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "DateTime", each DateTime.From([Date] + #duration(0,8,[Custom.Minute],[Custom.Second])), type datetime)

[v-kelly-msft · Accepted solution]

Hi  @smjzahid ,

Using below M codes to get the total hours and minutes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIyNLAyMFWK1YlWMrAyArINoGwDA6CUUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Total Time" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Total Time", type time}}, "zh-CN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "hours", each Time.Hour([Total Time])+Time.Minute([Total Time])/60+Time.Second([Total Time])/3600),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Total hours", each List.Sum(#"Added Custom"[hours])),
    #"Extracted Text Range" = Table.TransformColumns(#"Added Custom1", {{"Total hours", each Text.Middle(Text.From(_, "en-US"), 0, 5), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Range",{{"Total hours", type number}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Minutes", each Time.Hour([Total Time])*60+Time.Minute([Total Time])+Time.Second([Total Time])/60),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Total Minutes", each List.Sum(#"Added Custom2"[Minutes])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Minutes", "hours"})
in
    #"Removed Columns"

And you will see:

Or you could create 4 columns using dax expressions to get the result.

For details,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[mahoneypat]

Please see this article and videos.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[HotChilli]

A Time datatype in powerbi/power query is a time of the day e.g. 12.51pm  so adding them doesn't make sense.

If you can change the datatype to be duration in Power Query there are functions to get TotalHours or TotalMinutes.

In DAX, you can parse your time as a datetime or text and use an algorithm to work out total hours or minutes .  I think there are some blog posts on here about it.  There are definitely lots of posts about it.