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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
gvlado
Advocate III
Advocate III

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
Anonymous
Not applicable

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 III
Advocate III

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

Anonymous
Not applicable

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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