Difference of Time

Hi Anonymous ,

Check this file as an example: Download PBIX 

I've unpivoted the time values and calculated the minutes.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

See if this works. It returns the total minutes, which you can divide by 60 if you want to get hours and fractional hours.

This is the end result:

Paste the M code below into a blank query to see what I did:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3stA3MlAwNLAyMlPSUfLKz8gDUsEZmWklCsEliUUlIF5JflGqgiOQZWihFKuDpMvEytgCXZdrXgqKHiMDFD2WVgYGQFGf0uRKHBY5gTQZolpkjkUXskVgPUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateTime = _t, Author.FirstName = _t, TimeIn_Out = _t, Store = _t, Id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"Author.FirstName", type text}, {"TimeIn_Out", type text}, {"Store", type text}, {"Id", Int64.Type}}),
    #"Added End time" = Table.ExpandListColumn(Table.AddColumn(#"Changed Type", "End Time",
        each
            let varName = [Author.FirstName]
        in 
            Table.SelectRows(#"Changed Type", each [Author.FirstName] = varName and [TimeIn_Out] = "Shift End")[DateTime]),"End Time"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added End time",{{"DateTime", type datetime}, {"End Time", type datetime}}),
    #"Added Duration in Minutes" = Table.AddColumn(#"Changed Type1", "Duration", each Duration.TotalMinutes([End Time] - [DateTime])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Duration in Minutes",{{"Duration", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([TimeIn_Out] = "Shift Start"))
in
    #"Filtered Rows"

Hi Anonymous ,

We can use the following two measures with Author column in table visual to meet your requirement:

Hours =
DATEDIFF (
    CALCULATE ( MIN ( 'Table'[DateTime] ), 'Table'[Timeln_Out] = "Shift Start" ),
    CALCULATE ( MAX ( 'Table'[DateTime] ), 'Table'[Timeln_Out] = "Shift End" ),
    HOUR
)

Minutes =
DATEDIFF (
    CALCULATE ( MIN ( 'Table'[DateTime] ), 'Table'[Timeln_Out] = "Shift Start" ),
    CALCULATE ( MAX ( 'Table'[DateTime] ), 'Table'[Timeln_Out] = "Shift End" ),
    MINUTE
)

Best regards,