Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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.
Wow, thanks a lot
I will lern much from your example/ solution
You earned cup off coffe 🍵
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
76 | |
73 | |
42 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
42 |