Add x number of working days to a new start date

[Chris_23]

Hi guys,

I would like to calculate a new end date in Power Query.
Based on the net working days between the old start date and the old end date:

NETWORKDAYS(start date t0-ENDDTAE t0) = 180

ENDDATE New = Date.AddDays(Start date New,NETWORKDAYS(180))

Is there a way to solve this problem in Power Query?

Thanks a lot
Chris

[Tobi_Theilig]

Hi,

this function works amazing, but in my case i do not wand to add date i want to substract workings days from a date.

Can you please help me?

Thank you

[ogouret]

@mahoneypat 

Can you please explain your function ?

I used it successfully, but i'm new to Power BI and i'm trying to understand how it works , so I can add then the case of "holidays" in your function.

By the way I did not succeed to use your "non function" version, It says something like "workingdays is unknown".

[mahoneypat]

FYI that I had published a video on this solution that covers both this M function and a DAX solution to this scenario.

Power BI - Tales from the Front #03 - Due Date From Working Days - YouTube

Also, the non-function assumes that you have columns named startdate and workingdays (case sensitive).

@ogouret 

Pat

[mahoneypat · Accepted solution]

Not nearly as elegant as the one made by @ImkeF, but here is a simple function to get the end date given a start date and # of working days.  You can add this function in the query editor, name it something (e.g., fxWorkingDays), and then Invoke Custom Function on the Add Column tab.  Choose your start date and # of working days column to get the result (it just ignores Sat and Sun).

(startdate as date, workingdays as number) => 
  let
    Source = List.Select(
      List.Dates(
        Date.AddDays(startdate, 1), 
        workingdays + 1 + Number.RoundUp(workingdays / 5, 0) * 2, 
        #duration(1, 0, 0, 0)
      ), 
      each List.Contains({1..5}, Date.DayOfWeek(_))
    ){workingdays - 1}
  in
    Source

Or you can just add a custom column with this non-function version of the above

 = List.Select(
      List.Dates(
        Date.AddDays([startdate], 1), 
        [workingdays] + 1 + Number.RoundUp([workingdays] / 5, 0) * 2, 
        #duration(1, 0, 0, 0)
      ), 
      each List.Contains({1..5}, Date.DayOfWeek(_))
    ){workingdays - 1}

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[AndrewSinha]

Hi Thanks for this.  Can you please give me the code for subtracting x number of days.... Thnaks Andrew

[Greg_Deckler]

@Chris_23 ,    @ImkeF or @edhans might be able to help with Power Query. There are lots of DAX solutions for this sort of thing into the Quick Measures gallery: https://community.powerbi.com/t5/Quick-Measures-Gallery/bd-p/QuickMeasuresGallery, such as https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

[ImkeF]

Hi @Chris_23 

I created a Power Query function for it here: https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/