Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Im New to power BI and need help in translate excel formula below into a calculated column
The formula calclates working period between two dates excluding weekends and factors working hours
Example:
A | B | C | |
1 | 17/05/2019 14:46 | 21/05/2019 11:39 | 14:53:14 |
formula
=(NETWORKDAYS(A1,B1)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A2,1),"17:00","8:00")
results: 14:53:14 (hh:mm:ss)
In the formula, A1 is the start date time, B1 is the end date time, 8:00 and 17:00 are the general start time and end time in each day
Solved! Go to Solution.
Hi @Gplange,
this time I've tested more cases and I hope it works now.
let // calculates a duration between two timestamps in working hours fnDurationWorkingHours = (StartDateTime as datetime, EndDateTime as datetime) as duration => let // start and end date StartDate = Date.From(StartDateTime), EndDate = Date.From(EndDateTime), // start and end time StartTime = Time.From(StartDateTime), EndTime = Time.From(EndDateTime), // end of the shift on the first day and start of the shift on the last day ShiftEndOnStartDate = #time(17, 0, 0), ShiftStartOnEndDate = #time(8, 0, 0), // working days are from Mon=0 to Fri=4 fnIsWorkingDay = (dt as date) as logical => Date.DayOfWeek(dt, Day.Monday) < 5, // generate whole days between StartDateTime and EndDateTime DaysBetween = List.Generate( () => Date.AddDays(StartDate, 1), each _ < EndDate, each Date.AddDays(_, 1) ), // select only working days WorkingDaysBetween = List.Select(DaysBetween, fnIsWorkingDay), // get minimum of two times fnMin = (first as time, second as time) as time => if first > second then second else first, // get maximum of two times fnMax = (first as time, second as time) as time => if first > second then first else second, // duration on the first day StartDateWorkingHours = if not fnIsWorkingDay(StartDate) then #duration(0, 0, 0, 0) else if ShiftEndOnStartDate < StartTime then #duration(0, 0, 0, 0) else ShiftEndOnStartDate - fnMax(StartTime, ShiftStartOnEndDate), // duration on the days between WorkingHoursBetween = #duration(0, List.Count(WorkingDaysBetween) * 9, 0, 0), // duration on the last day EndDateWorkingHours = if not fnIsWorkingDay(EndDate) then #duration(0, 0, 0, 0) else if EndTime < ShiftStartOnEndDate then #duration(0, 0, 0, 0) else fnMin(EndTime, ShiftEndOnStartDate) - ShiftStartOnEndDate, // sum it up Result = if StartDateTime > EndDateTime then #duration(0, 0, 0, 0) else if StartDate = EndDate and not fnIsWorkingDay(StartDate) then #duration(0, 0, 0, 0) else if StartDate = EndDate then fnMin(EndTime, ShiftEndOnStartDate) - fnMax(StartTime, ShiftStartOnEndDate) else StartDateWorkingHours + WorkingHoursBetween + EndDateWorkingHours in Result, // test data StartDateTime = #datetime(2019, 5, 17, 18, 46, 0), EndDateTime = #datetime(2019, 5, 18, 18, 39, 0) in fnDurationWorkingHours(StartDateTime, EndDateTime)
Hi @TimWilsens,
another approach here (you can see how to use it at the bottom of this post).
You can edit Shift step according to your needs.
Result:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc3bDYAgEETRVsx+kzCzPIRthdB/GyIaMfye3My0Jjw9klewHowWs0HFiXIpLVRjlu7a34sFGLDF56BbZxyX43W3q46ZWUM98uMos06jJj9lno9Jer8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}, "sk-SK"),
// Edit according to your needs.
Shift = [ start = #time(8, 0, 0), end = #time(17, 0, 0) ],
StepBack = ChangedType,
Ad_DaysWithoutWeekends =
[ ls = List.Buffer(StepBack[Start]),
le = List.Buffer(StepBack[End]),
lg = List.Generate(
()=> [ x = 0, start = ls{x}, end = le{x} ],
each [x] < List.Count(ls),
each [ x = [x]+1, start = ls{x}, end = le{x} ],
each List.Select(
List.Transform( { Number.RoundDown(Number.From([start]), 0)..Number.RoundDown(Number.From([end]), 0) }, Date.From ),
each Date.DayOfWeek(_, Day.Monday) < 5 //working days only
)
),
merged = Table.FromColumns(Table.ToColumns(StepBack) & {lg}, Table.ColumnNames(StepBack) & {"Working Days"})
][merged],
Ad_WorkedDuration = Table.AddColumn(Ad_DaysWithoutWeekends, "Worked Duration", each
[ fullDays = List.Count(List.RemoveLastN(List.RemoveFirstN([Working Days]))),
fullDaysHours = if fullDays > 0 then fullDays * ( Time.Hour(Shift[end]) - Time.Hour(Shift[start]) ) else 0,
firstDayTime = Shift[end] - Time.From([Start]),
lastDayTime = Time.From([End]) - Shift[start],
workedDuration = Duration.From(fullDaysHours / 24) +
( if fullDays = 0 then Time.From([End]) - Time.From([Start])
else firstDayTime + lastDayTime )
][workedDuration],
type duration),
Ad_WorkedDays = Table.AddColumn(Ad_WorkedDuration, "Worked Days", each Duration.TotalDays([Worked Duration]), type number),
Ad_WorkedHours = Table.AddColumn(Ad_WorkedDays, "Worked Hours", each Duration.TotalHours([Worked Duration]), type number),
#"RemovedColumns" = Table.RemoveColumns(Ad_WorkedHours,{"Working Days"})
in
#"RemovedColumns"
Not 100% tested, but...
Add a custom column with the following code:
let workHoursThisWeek = (t) => let dayOfWeek = Date.DayOfWeek(t), timeOfDay = DateTime.Time(t) in if dayOfWeek = 0 then #duration(0,0,0,0) else if dayOfWeek = 6 then 45 * #duration(0,1,0,0) else ((dayOfWeek - 1) * 9 * #duration(0,1,0,0) + (( if timeOfDay < #time(8,0,0) then #time(8,0,0) else if timeOfDay > #time(17,0,0) then #time(17,0,0) else timeOfDay) - #time(8,0,0))) in (Date.StartOfWeek([B]) - Date.StartOfWeek([A])) * (9 * 5) / (7 * 24) - workHoursThisWeek([A]) + workHoursThisWeek([B])
Hi @Gplange,
I've written another code which is maybe more complicated, but straight forward. But I'd like to recommend you to use a separate date dimension because you can handle exceptions like holidays, bridge days, and so on.
let // calculates a duration between two timestamps in working hours fnDurationWorkingHours = (StartDateTime as datetime, EndDateTime as datetime) as duration => let // start and end date StartDate = Date.From(StartDateTime), EndDate = Date.From(EndDateTime), // working days are from Mon=0 to Fri=4 fnIsWorkingDay = (dt as date) as logical => Date.DayOfWeek(dt, Day.Monday) < 5, // generate whole days between StartDateTime and EndDateTime DaysBetween = List.Generate( () => Date.AddDays(StartDate, 1), each _ < EndDate, each Date.AddDays(_, 1) ), // select only working days WorkingDaysBetween = List.Select(DaysBetween, fnIsWorkingDay), // end of the shift on the first day and start of the shift on the last day ShiftEndOnStartDate = StartDate & #time(17, 0, 0), ShiftStartOnEndDate = EndDate & #time(8, 0, 0), // duration on the first day StartDateWorkingHours = if not fnIsWorkingDay(StartDate) then #duration(0, 0, 0, 0) else if ShiftEndOnStartDate < StartDateTime then #duration(0, 0, 0, 0) else ShiftEndOnStartDate - StartDateTime, // duration on the days between WorkingHoursBetween = #duration(0, List.Count(WorkingDaysBetween) * 9, 0, 0), // duration on the last day EndDateWorkingHours = if not fnIsWorkingDay(EndDate) then #duration(0, 0, 0, 0) else if EndDateTime < ShiftStartOnEndDate then #duration(0, 0, 0, 0) else EndDateTime - ShiftStartOnEndDate, // sum it up Result = StartDateWorkingHours + WorkingHoursBetween + EndDateWorkingHours in Result, // test data StartDateTime = #datetime(2019, 5, 17, 14, 46, 0), EndDateTime = #datetime(2019, 5, 21, 11, 39, 0) in fnDurationWorkingHours(StartDateTime, EndDateTime)
Hello @Nolock Thanks a lot .
It worked but i noticed the formula adds 9 hours to the time when the StartDate and Enddate are the same day . I did a quick check with results in Excel. It working perfectly when the days are different. Attached a screen shot I've highlighted the wrong ones in red and correct ones in green. Please help
Really appreciate the help . Thanks a gain
Hi @Gplange,
this time I've tested more cases and I hope it works now.
let // calculates a duration between two timestamps in working hours fnDurationWorkingHours = (StartDateTime as datetime, EndDateTime as datetime) as duration => let // start and end date StartDate = Date.From(StartDateTime), EndDate = Date.From(EndDateTime), // start and end time StartTime = Time.From(StartDateTime), EndTime = Time.From(EndDateTime), // end of the shift on the first day and start of the shift on the last day ShiftEndOnStartDate = #time(17, 0, 0), ShiftStartOnEndDate = #time(8, 0, 0), // working days are from Mon=0 to Fri=4 fnIsWorkingDay = (dt as date) as logical => Date.DayOfWeek(dt, Day.Monday) < 5, // generate whole days between StartDateTime and EndDateTime DaysBetween = List.Generate( () => Date.AddDays(StartDate, 1), each _ < EndDate, each Date.AddDays(_, 1) ), // select only working days WorkingDaysBetween = List.Select(DaysBetween, fnIsWorkingDay), // get minimum of two times fnMin = (first as time, second as time) as time => if first > second then second else first, // get maximum of two times fnMax = (first as time, second as time) as time => if first > second then first else second, // duration on the first day StartDateWorkingHours = if not fnIsWorkingDay(StartDate) then #duration(0, 0, 0, 0) else if ShiftEndOnStartDate < StartTime then #duration(0, 0, 0, 0) else ShiftEndOnStartDate - fnMax(StartTime, ShiftStartOnEndDate), // duration on the days between WorkingHoursBetween = #duration(0, List.Count(WorkingDaysBetween) * 9, 0, 0), // duration on the last day EndDateWorkingHours = if not fnIsWorkingDay(EndDate) then #duration(0, 0, 0, 0) else if EndTime < ShiftStartOnEndDate then #duration(0, 0, 0, 0) else fnMin(EndTime, ShiftEndOnStartDate) - ShiftStartOnEndDate, // sum it up Result = if StartDateTime > EndDateTime then #duration(0, 0, 0, 0) else if StartDate = EndDate and not fnIsWorkingDay(StartDate) then #duration(0, 0, 0, 0) else if StartDate = EndDate then fnMin(EndTime, ShiftEndOnStartDate) - fnMax(StartTime, ShiftStartOnEndDate) else StartDateWorkingHours + WorkingHoursBetween + EndDateWorkingHours in Result, // test data StartDateTime = #datetime(2019, 5, 17, 18, 46, 0), EndDateTime = #datetime(2019, 5, 18, 18, 39, 0) in fnDurationWorkingHours(StartDateTime, EndDateTime)
@Nolock Thank you. You have no idea how you've saved my life with this solution. THanks again
Hi, can you explain me how I can implement this in PowerBI? This would really help me out as I exactly like you need to calcualte the difference between two date/times exlcuding weekends.
Many thanks in advance!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.