Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!