How to split working hours into the days and shifts

[gvlado]

I would like to become PBI expert. Like to learn but can't solve the folowing problem - hope someone can help.
Problem description.:
If work started on one day and finished few days in the future
How to split work on days and on shifts.
Also the problem is to add time from night shift to previous calendar day. 
Illustration of input data/table and desired table

Thanks for your effort
Vlado

[gvlado]

Wow, thanks a lot
I will lern much from your example/ solution
You earned cup off coffe 🍵

[v-rongtiep-msft · Accepted solution]

Hi @gvlado ,

I have created a sample, please refer to it to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtIHQgsFJR0Ix1jfCMg0MLUyMAAiENPAyhDKRKhWQFIA06YAVxkbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"start date" = _t, #"end date" = _t, #"start time" = _t, #"end time" = _t, #"start times" = _t, #"end times" = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"start times", type datetime}, {"end times", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [

a= [end times]-[start times],
b=Duration.TotalHours(a),
c=List.DateTimes([start times], b, #duration(0,1,0,0))
][c]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each DateTime.Time([Custom])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom.1", "time"}, {"Custom", "dates"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"start date", "start time", "end date", "end time", "start times", "end times", "dates", "time", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns", "date", each DateTime.Date([dates])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"end time", type time}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"start date", "start time", "end date"})
in
    #"Removed Columns"

Then create columns.

date_col = IF(input[time]<=TIME(15,00,00)&&input[time]>TIME(7,00,00),input[date],IF(input[time]<=TIME(23,00,00)&&input[time]>TIME(15,00,00),input[date],input[date]-1))

shift = IF(input[time]<=TIME(15,00,00)&&input[time]>TIME(7,00,00),"shift 1",IF(input[time]<=TIME(23,00,00)&&input[time]>TIME(15,00,00),"shift 2","shift 3"))

Finally create measures.

result = var _maxtime= CALCULATE(MAX(input[time]),FILTER(ALL(input),input[date_col]=SELECTEDVALUE(input[date_col])&&input[shift]=SELECTEDVALUE(input[shift])))
var _mintime= CALCULATE(MIN(input[time]),FILTER(ALL(input),input[date_col]=SELECTEDVALUE(input[date_col])&&input[shift]=SELECTEDVALUE(input[shift])))
var _time=IF(_maxtime=_mintime,MAX(input[end time]),_maxtime)
var _re=_maxtime-_mintime
var _1re=IF(_re<TIME(07,00,00),_re,_re+TIME(1,00,00))
return
IF(_maxtime=_mintime&&MAX(input[shift])="shift 1",MAX(input[end time])-TIME(7,00,00),IF(_maxtime=_mintime&&MAX(input[shift])="shift 2",MAX(input[end time])-TIME(15,00,00),IF(_maxtime=_mintime&&MAX(input[shift])="shift 3",MAX(input[end time])+TIME(1,00,00),_1re)))

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.