Duration Excluding exception, holidays and non-working work week

[San_Raz]

I need to calculate net duration in hrs between two time pweriods.
There are three tables,
one with a "standard work week" table - as shown below 

Calender ID	Standard Work Week	Work/Non_working	Start	Finish	Timeperiod (Hrs)
C1	Sunday	Work	6:00:00 AM	6:00:00 PM	12
C1	Monday	Work	12:00:00 AM	11:59:00 PM	24
C1	Tuesday	Work	12:00:00 AM	11:59:00 PM	24
C1	Wednesday	Work	12:00:00 AM	11:59:00 PM	24
C1	Thursday	Work	12:00:00 AM	11:59:00 PM	24
C1	Friday	Work	12:00:00 AM	11:59:00 PM	24
C1	Saturday	Non_work
C2	Sunday	Non-Work
C2	Monday	Work	12:00:00 AM	11:59:00 PM	24
C2	Tuesday	Work	12:00:00 AM	11:59:00 PM	24
C2	Wednesday	Work	12:00:00 AM	11:59:00 PM	24
C2	Thursday	Work	12:00:00 AM	11:59:00 PM	24
C2	Friday	Work	12:00:00 AM	11:59:00 PM	24
C2	Saturday	Work	12:00:00 AM	11:59:00 PM	24

Second table is a "holiday/Exception" table as shown below 

Calender ID	Date	Work Week	Holiday/Exception	Start	End	Timeperiod (Hrs)
C1	03-04-24	Wednesday	Exception	6:00:00 AM	6:00:00 PM	12
C1	07-04-24	Sunday	Holiday			0
C2	08-04-24	Monday	Holiday			0

In the third table is where i am having the time period against which I need to find out the duration in Hrs.

Activty	Calnder ID	Start Date	End date	Duration	Net Duration (exclusive holidays/Exceptions and Non-Work)
A	C1	03-04-24 8:00	08-04-24 10:00	122.00	82
B	C2	03-04-24 8:00	08-04-24 10:00		?

In short, while calculating the Net-duration, both the two table has to be refered.
below is the logics, how the net duration is arrived at.

for calender  C1 ,
3-04-2024- It is wednesday and as per table 1 its a normal working day with 24 hrs but  as per table 2, it is an exception so based on it the duration for that day from 8 AM to  6PM = 10 hrs
04-04-2024 -It is thursday, and  as per table 1 its a normal working day  and and there is no exception for it as per table 2- 24hrs
05-04-2024 -It is Friday and  as per table 1 its a normal working day  and and there is no exception for it as per table 2- 24hrs
06-04-2024 -it is saturday non-work  as per table 1 and there is no exception for it as per table 2- 0 hrs
07-04-2024- It is a sunday and as per table 1 it is  working day with 12 hrs but  from table 2 it is a exception with a

holiday So- 0hrs 
08-04-2024 - It is Monday and as per table 1 it is a normal working day and there there is no exception for it as per table 2, so duration until 10:00 AM - 10 hrs  (Note: it is exception for calender C2).

I need to arrive at the net duration, Kindly help me with a solution and if you can atatch the .pbix file, it would be great.
Thanks in advance

[dufoq3 · Accepted solution]

Hi @San_Raz, are you sure that you've provided correct expected result?

This is my output

based on this logic:

• Duration = based on STANDARD WORK WEEK TABLE only
• Net Duration = based on STANDARD WORK WEEK TABLE and EXCEPTIONS TABLE

You have to replace these 3 tables with your table references (if you don't know how - read Note below my post)

let
    WeekStd = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lEKLs1LSawEMsLzi7KBlJmVgQEQKTj6InECQBxDI6VYHagu33xUXYZGyNoMDa1MLeH6jEwQ+kJKU4vJ0hiempJHptaQjNIi8nS6FWWSpS84saS0CKLTLz8vvhyiWwGOwQqNkAMfqEw3HKcyckLbiNzQNiI/tI3IDm0jMkPbCDW0idQZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calender ID" = _t, #"Standard Work Week" = _t, #"Work/Non_working" = _t, Start = _t, Finish = _t, #"Timeperiod (Hrs)" = _t]),
    Exceptions = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lEyMNY1MNE1MgEyw1NT8lKLUxIrgWzXiuTUgpLM/Dwg28zKwACIFBx9kTgBII6hkVKsDswgc7hBwaV5EFM88nMyISwFKDaAaDACMS3gGnzzMTRAEFB5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calender ID" = _t, Date = _t, #"Work Week" = _t, #"Holiday/Exception" = _t, Start = _t, End = _t, #"Timeperiod (Hrs)" = _t]),
    WeekStdChT = Table.RenameColumns(Table.TransformColumnTypes(WeekStd,{{"Start", type time}, {"Finish", type time}}), {{"Calender ID", "Calendar ID"}}, MissingField.Ignore),
    ExceptionsChT = Table.RenameColumns(Table.TransformColumnTypes(Exceptions,{{"Date", type date}, {"Start", type time}, {"End", type time}}), {{"Calender ID", "Calendar ID"}}, MissingField.Ignore),
    H = [ fn_ReplaceNullTimes = (tbl, startColName, endColName)=>  Table.TransformColumns(tbl, {{startColName, each if _ = null then #time(0,0,0) else _, type time}, {endColName, each if _ = null then #time(0,0,0) else _, type time}}), //Replace null times to 0:00:00 time
    WeekStd = Table.Buffer(fn_ReplaceNullTimes(Table.SelectColumns(WeekStdChT,{"Calendar ID", "Standard Work Week", "Start", "Finish"}), "Start", "Finish")),
    Exceptions = Table.Buffer(fn_ReplaceNullTimes(Table.SelectColumns(ExceptionsChT,{"Calendar ID", "Date", "Start", "End"}), "Start", "End")) ],
    SummaryTbl = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI2BBIGxroGJrpGJgoWVgYGIL4FlG9oABKI1YlWcgKpNSJCbSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Activty = _t, #"Calnder ID" = _t, #"Start Date" = _t, #"End date" = _t]),
    SummaryTblChT = Table.RenameColumns(Table.TransformColumnTypes(SummaryTbl,{{"Start Date", type datetime}, {"End date", type datetime}}), {{"End date", "End Date"}, {"Calnder ID", "Calendar ID"}, {"Calender ID", "Calendar ID"}}, MissingField.Ignore),
    Ad_helper = Table.AddColumn(SummaryTblChT, "helper", each 
        [ sDate = Date.From([Start Date]),
          eDate = Date.From([End Date]),
          dates = Table.AddColumn(Table.FromList(List.Dates(sDate, Duration.TotalDays(eDate-sDate)+1, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[ActDate=date]), "ActCalendar ID", (x)=> [Calendar ID], type text),
          mergeExceptions = Table.ExpandTableColumn(Table.NestedJoin(dates, {"ActDate", "ActCalendar ID"}, H[Exceptions], {"Date", "Calendar ID"}, "Exceptions", JoinKind.LeftOuter), "Exceptions", {"Start", "End"}, {"ExceptionStart", "ExceptionEnd"}),
          Ad_DayName = Table.AddColumn(mergeExceptions, "DayName", (x)=> Date.DayOfWeekName(x[ActDate], "en-US")),
          mergeWeekStd = Table.ExpandTableColumn(Table.NestedJoin(Ad_DayName, {"DayName", "ActCalendar ID"}, H[WeekStd], {"Standard Work Week", "Calendar ID"}, "WeekStd", JoinKind.LeftOuter), "WeekStd", {"Start", "Finish"}, {"WeekStdStart", "WeekStdEnd"}),
          Ad_NetStart = Table.AddColumn(mergeWeekStd, "DurationNetStart", (x)=> if x[ActDate] = sDate then List.Max({x[ExceptionStart], Time.From([Start Date])}) else List.First(List.RemoveNulls({x[ExceptionStart], x[WeekStdStart]})), type datetime),
          Ad_NetEnd = Table.AddColumn(Ad_NetStart, "DurationNetEnd", (x)=> if x[ActDate] = eDate then List.Min({x[ExceptionEnd], Time.From([End Date])}) else List.First(List.RemoveNulls({x[ExceptionEnd], x[WeekStdEnd]})),type datetime),
          NetDuration = Table.AddColumn(Ad_NetEnd, "NetDuration", (x)=> let dur = Duration.TotalHours(x[DurationNetEnd]-x[DurationNetStart]) in if Time.Minute(x[DurationNetEnd]) = 59 then Number.RoundUp(dur, 0) else dur),
          Ad_Start = Table.AddColumn(NetDuration, "DurationStart", (x)=> if x[ActDate] = sDate then Time.From([Start Date]) else x[WeekStdStart], type datetime),
          Ad_End = Table.AddColumn(Ad_Start, "DurationEnd", (x)=> if x[ActDate] = eDate then Time.From([End Date]) else x[WeekStdEnd], type datetime),
          DurationTbl = /* Table.Sort( */Table.AddColumn(Ad_End, "Duration", (x)=> let dur = Duration.TotalHours(x[DurationEnd]-x[DurationStart]) in if Time.Minute(x[DurationEnd]) = 59 then Number.RoundUp(dur, 0) else dur)/* , {{"ActDate", Order.Ascending}}) */,
          Result = [ NetDuration = List.Sum(DurationTbl[NetDuration]), Duration = List.Sum(DurationTbl[Duration]) ]
        ], type record ),
    ExpandedHelper = Table.ExpandRecordColumn(Ad_helper, "helper", {"DurationTbl", "Result"}, {"DurationTbl", "Result"}),
    ExpandedResult = Table.ExpandRecordColumn(ExpandedHelper, "Result", {"NetDuration", "Duration"}, {"NetDuration", "Duration"})
in
    ExpandedResult

[Greg_Deckler]

@San_Raz I created something like this once. See if you can adapt it to your situation:

Net Work Duration (Working Hours) - Microsoft Fabric Community

[lbendlin]

What do you need C1 and C2 for?  Why not use real dates?

Then you can do a series of INTERSECT and EXCEPT to calculate the number of matching hour identifiers. Do you want this in Power Query or in DAX?

[San_Raz]

Actually the calendar ID's are required as in the project there are different calenders to be used by different departments. And each department calender have different holidays and exception.

In the activity table, against each activity, the calendar IDs are mentioned so that net duration can be calculated considering the exception and work week for that calender 

I prefer any solution either in query or dax 

[San_Raz]

And here I have only mentioned one activity, say if there is activity B which takes calender id , C2 then the exception and workweeks need to be based on C2.

Hope you got the clarity regarding Calender Id