Difference between Time on first and last value of that day

[Anonymous]

Hi There,

I am trying to calculate the amount of hours someone has spent working by using the timestamp of the last date field used on that day minus the first timestamp on that day.

Hopping to accomplish with this the avg. amount of hours worked per name, per week/month/year etc after.

NAME     |     Date                        |

User A     |  01-01-2020 08:00:00 | 

User A     |  01-01-2020 13:00:00 |

User A     |  01-01-2020 20:00:00 |

User A     |  02-02-2020 06:00:00 |

User A     |  02-02-2020 08:00:00  |

In the example above,

• User A would retrieve 12hrs (High: 20:00:00 - Min 8:00:00) for day 01-01-2020
• User A would retrieve 2hrs (High: 8:00:00 -Min 06:00:00 ) for day 02-02-2020

Tried a few different thinks and don't seem to make it work, anyone has any advice?

Thanks

[Greg_Deckler]

So, you should be able to do something similar to MTBF here: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

And you should be able to use DATEDIFF with HOURS.

Other stuff:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639?search-a...

[Mariusz · Accepted solution]

Hi @Anonymous 

If you are looking to achieve the below?

Then you can use this code and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVJwVNJRMjDUByIjAyMDBQMLKwMDIFKK1cGuwNCYgAIjA6wKjPSBCGKFGSEFcDfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NAME = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NAME", type text}, {"Date", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date.1", each Date.From( [Date] ), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"NAME", "Date.1"}, {{"min", each List.Min([Date]), type datetime}, {"max", each List.Max([Date]), type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Duration", each Duration.Hours( [max] - [min] ), Int64.Type ),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Date.1", "Date"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"NAME", "Date", "Duration"})
in
    #"Removed Other Columns"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn