Hello,
After seeing this following post and in order to not reopen it, I tried the function explained at the bottom but it returns sometimes suspicious values.
Remind, here's the function :
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
ResultBut for example, when I have in start and end time these
There's no 6 days and 20h betweend 19/09/2022 and 13/10/2022, even if we exclude weekends.
Some other examples :
Could you help me please ?
Thank you!
1 ACCEPTED SOLUTION
Hello,
I tried your solution but it seems to have problems in it :
Some results are wrong (between 3 and 17, there's not only 3 days 😞 )
Check this. Ignore first 4 rows (I've added them just for test purpose).
You can edit shiftStart and shiftEnd in Helper step.
EDIT: I've added option where you can decide whether you want to include or exclude weekends:
Output
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVZRbuwwCLxKtd+rLGA7iX2Vam9Q9e/d/zng2NQmaVJppRUTDMMATj4/H7i+IL4IiD5gTgESwOP5IKgorskXtPzez3wsmsfcjWPxT8cQkisOGqWKHmRb89PxmE+ZBlJGYcp17MZWPm4u7gOXBJjCYh3M6JYGdvr+R5rlBxqT22K4CZvx/e/ry/KEKPTJjNtQYeGCos/GHld58kMIwne24ioUkatzEtc1Y+5S+2ChFM/Udy+E2sr8y8U+H/NEzdj0XIv6IeWz0iADvVYNNKoTUDUQuoIdWmg4q6YoXf7U1nAXE64H6NWIe2DXhyiTF83a4zhPXDs0o7lsYpZ5HFCcL+6ReJc0SzVGF7RQwIvVxEZ1Ajzh7S00yPibCkPbFWKZgj9Ab1ws3qvNZAPzWFIb4jyMvrttpUdwRxBPShAx+tK9KYi7pTsElUaMQeHZTC6LqvegpdG1CylQU+R6F0F70RgNJQ1eWNT4Qqz7tO25s9AyMHZEWjriPDE4YawGrX1fcefAU4SW7gqVW9uvIshaDe3CpYOJ5jv7+PVkb4Hrek50h2lAxZSNgZOLJopnQmMnNOkJYYOodi4zkc5xP9VnhVqrPwkiHbwsCJPz5RUazbc31O+NAQ3zrTTLr2n4nhnTXNX9oJrBha/jEYU71RCeVYP1WhjQe72h+GsaXtcBLWt16Z7BbgL5M2pAs7Dv938=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [début = _t, dateEC = _t, DiffEC = _t, dateT = _t, dateF = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"dateF", type datetime}, {"dateT", type datetime}, {"DiffEC", type duration}, {"dateEC", type datetime}, {"début", type datetime}}),
Helper = [ shiftStart = #time(8,0,0),
shiftEnd = #time(17,0,0),
includeWeekends = "no", //type "yes" if you want to include weekends. Type "no" if you want to exclude weekends
workingHours = shiftEnd - shiftStart ],
fn_WorkingDuration =
(previousStep as table, start as text, end as text, optional colName as text)=>
let
// Ad_StartColumn = Table.DuplicateColumn(ChangedType, "début", "S"),
// Ad_EndColumn = Table.DuplicateColumn(Ad_StartColumn, "dateEC", "E"),
Ad_StartColumn = Table.DuplicateColumn(previousStep, start, "S"),
Ad_EndColumn = Table.DuplicateColumn(Ad_StartColumn, end, "E"),
Ad_SDate = Table.AddColumn(Ad_EndColumn, "S Date", each DateTime.Date([S]), type date),
Ad_STime = Table.AddColumn(Ad_SDate, "S Time", each DateTime.Time([S]), type time),
Ad_EDate = Table.AddColumn(Ad_STime, "E Date", each DateTime.Date([E]), type date),
Ad_ETime = Table.AddColumn(Ad_EDate, "E Time", each DateTime.Time([E]), type time),
// Weekends excluded
Ad_WorkingDays = Table.AddColumn(Ad_ETime, "Working Days", each try
[ a = List.Dates([S Date], Duration.TotalDays([E Date] - [S Date]) +1, #duration(1,0,0,0)), //weekends included
b = List.Select(a, (x)=> Date.DayOfWeek(x, Day.Monday) < 5), //weekends excluded
c = if Text.Trim(Text.Lower(Helper[includeWeekends])) = "yes" then a else b
][c] otherwise null, type list),
// [S Date] and [E Date] excluded
Ad_FullDaysHours = Table.AddColumn(Ad_WorkingDays, "Full Days Hours", each try
[ a = List.RemoveItems([Working Days], { [S Date], [E Date]}),
b = Helper[workingHours] * List.Count(a)
][b] otherwise null, type duration),
Ad_FirstDayHours = Table.AddColumn(Ad_FullDaysHours, "Fist Day Hours", each try if [S Date] = List.First([Working Days])
then (if [S Date] = [E Date] then List.Min({ [E Time], Helper[shiftEnd] }) else Helper[shiftEnd]) - List.Max({ [S Time], Helper[shiftStart] })
else #duration(0,0,0,0) otherwise null, type duration),
Ad_LastDayHours = Table.AddColumn(Ad_FirstDayHours, "Last Day Hours", each try if [S Date] = [E Date] then #duration(0,0,0,0) else if [E Date] = List.Last([Working Days])
then Helper[workingHours]
else #duration(0,0,0,0) otherwise null, type duration),
// Ad_TotalWorkingDuration = Table.AddColumn(Ad_LastDayHours, "Total Working Duration", each [Full Days Hours] + [Fist Day Hours] + [Last Day Hours], type duration),
Ad_TotalWorkingDuration = Table.AddColumn(Ad_LastDayHours, if colName <> null then colName else "Total Working Duration" , each [Full Days Hours] + [Fist Day Hours] + [Last Day Hours], type duration),
_RemovedColumns = Table.RemoveColumns(Ad_TotalWorkingDuration,{"S", "E", "S Date", "S Time", "E Date", "E Time", "Working Days", "Full Days Hours", "Fist Day Hours", "Last Day Hours"})
in
_RemovedColumns,
StepBack = ChangedType,
Ad_dateECDuration = fn_WorkingDuration(StepBack, "début", "dateEC", "dateEC duration"),
Ad_dateTDuration = fn_WorkingDuration(Ad_dateECDuration, "début", "dateT", "dateT duration"),
Ad_dateFDuration = fn_WorkingDuration(Ad_dateTDuration, "début", "dateF", "dateF duration")
in
Ad_dateFDuration
I removed ChangedType because in function, it allows only 3 or 4 parameter. I let Helper but I got this now :
I refreshed the post, but in your function, I still see only 4 parameters :
Helpful resources
Fabric Community Update - November 2024
Find out what's new and trending in the Fabric Community.
Be one of the first to start using Fabric Databases
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Power BI Monthly Update - November 2024
Check out the November 2024 Power BI update to learn about new features.
