Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gvlado
Advocate II
Advocate II

How to split working hours into the days and shifts

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

gvlado_0-1670240683099.png

Thanks for your effort
Vlado

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

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)))

vpollymsft_1-1670314985489.png

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.

View solution in original post

2 REPLIES 2
gvlado
Advocate II
Advocate II

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

v-rongtiep-msft
Community Support
Community Support

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)))

vpollymsft_1-1670314985489.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.