none
Calculate Working Days using Power Query RRS feed

  • Question

  • Is there a way to calculate working days ( i.e excluding weekends ) using Power Query between any 2 random dates?

    Also if the start date or end date is a weekend I need to subtract this too..

    • Edited by Olivia127 Wednesday, January 27, 2016 8:45 AM
    Wednesday, January 27, 2016 8:42 AM

Answers

  • Hi Olivia :-)

    Cuple months ago I wrote something like this:

    let
        fxWorkingDays = (start as date, end as date, optional HDays as list) as number =>
            let
               LstOfHolidays = if HDays = null then {} else HDays,
               LstOfDays = {Number.From(start)..Number.From(end)},
               LstDiff  = List.Difference(LstOfDays, LstOfHolidays),
               LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),
               LstSel = List.Select(LstMod, each _>1),
               Result = List.Count(LstSel)
             in
               Result,
        Holidays_Import =  try Excel.CurrentWorkbook(){[Name="Holidays"]}[Content] otherwise Table.FromColumns({{}}, {"Holidays"}),
        Holidays = List.Buffer(Table.TransformColumnTypes(Holidays_Import,{{"Holidays", type number}})[Holidays]),
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
        NumberOfWorkingDays = Table.AddColumn(ChType, "Workdays", each fxWorkingDays([StartDate], [EndDate], Holidays))
    in
        NumberOfWorkingDays

    "Table1" contains raw data ( "StartDate" and "EndDate").
    Table "Holidays" if exist, contains dates of holiday.
    The syntax of the function "fxWorkingDays" is exactly the same as Excel NetworkDays function.
    It does not require  calendar table, and the table of holidays is optional.

    Regards :-)

    • Marked as answer by Olivia127 Thursday, January 28, 2016 5:33 AM
    Wednesday, January 27, 2016 5:34 PM

All replies

  • Hi Olivia :-)

    Cuple months ago I wrote something like this:

    let
        fxWorkingDays = (start as date, end as date, optional HDays as list) as number =>
            let
               LstOfHolidays = if HDays = null then {} else HDays,
               LstOfDays = {Number.From(start)..Number.From(end)},
               LstDiff  = List.Difference(LstOfDays, LstOfHolidays),
               LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),
               LstSel = List.Select(LstMod, each _>1),
               Result = List.Count(LstSel)
             in
               Result,
        Holidays_Import =  try Excel.CurrentWorkbook(){[Name="Holidays"]}[Content] otherwise Table.FromColumns({{}}, {"Holidays"}),
        Holidays = List.Buffer(Table.TransformColumnTypes(Holidays_Import,{{"Holidays", type number}})[Holidays]),
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
        NumberOfWorkingDays = Table.AddColumn(ChType, "Workdays", each fxWorkingDays([StartDate], [EndDate], Holidays))
    in
        NumberOfWorkingDays

    "Table1" contains raw data ( "StartDate" and "EndDate").
    Table "Holidays" if exist, contains dates of holiday.
    The syntax of the function "fxWorkingDays" is exactly the same as Excel NetworkDays function.
    It does not require  calendar table, and the table of holidays is optional.

    Regards :-)

    • Marked as answer by Olivia127 Thursday, January 28, 2016 5:33 AM
    Wednesday, January 27, 2016 5:34 PM
  • Alternately, you can create a function that does this without needing to perform joins:
    let
      WorkDaysBetween = (start as date, end as date, dimdate as table) =>
        Table.RowCount(
          Table.SelectRows(
            dimdate
            ,each
              ( ([Date] >= start and [Date] <= end)
                and [WorkDayFlag] = true )
          )
        )
    in
      WorkDaysBetween

    This should get you there, it might need some minor tweaking.

    Check out the Power BI User Group of Philadelphia.
    Our next meeting is February 2 in Malvern, PA.

    Wednesday, January 27, 2016 5:37 PM
  • Thank you all..

    Bill, it worked with some tweaks. Thank you :-)

    Thursday, January 28, 2016 5:34 AM
  • Please, I can notwork it out:

    I have twotables in Power BI Desktop

    How do Iapply thissolution?

    I need of working days between two dates, but I can not apply the function.

     dates

    start end
    25/01/2016    26/02/2016
    26/12/2015 15/01/2016
    15/12/2015 25/01/2016

    holidays

    holidays
    01/01/2016
    24/12/2015
    25/12/2015
    Wednesday, February 3, 2016 5:01 PM
  • Hello Bill,

    Your post helps me a lot, in a fact your logic to find working days is brilliant, that was the clue for me, and honestly it tooks me some time to understand it. But base on it I have simplified only to function which can be used directly in custom column in any query and includes also negative values (delays). So here is:

    let WorkingDays = (start as date, end as date, optional HDays as table) as number =>
      
    let
      LstOfHolidays = if HDays = null then {} else Table.Column(HDays,"NameOfYourCalendarColumn"),
      NumLstOfHolidays = List.Transform(LstOfHolidays ,each Number.From(_)),
      LstOfDays = if start<end then {Number.From(start)..Number.From(end)} else {Number.From(end)..Number.From(start)},
      LstDiff  = List.Difference(LstOfDays, NumLstOfHolidays),
      LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),
      LstSel = List.Select(LstMod, each _>1) ,
      Result = if start<end then List.Count(LstSel) else List.Count(LstSel)*(-1)
    in
      Result
    in
      WorkingDays

    Where:

    start – Start date

    end – End date

    HDays – Is table with holidays, where one column has holidays from one calendar . In my case are different subcontractors calendars, each one in his proper column.

    Regards





    • Edited by KraBo Monday, December 12, 2016 9:13 PM
    Monday, September 26, 2016 9:27 AM
  • Hello Bill

    As KraBo mentiones above, the function sometimes produces negative figures for a sameday result. 

    I have 14/05/2018 for RECEIVEDDATE and the same 14/05/2018 for Date Completed, and my answer is -1 ??

    = Table.AddColumn(#"Removed Columns", "WorkingDays", each fnWorkDays([RECEIVEDDATE], [Date Completed], API_UKHolidays))

    Thursday, September 20, 2018 1:05 PM