topic Re: NETWORK DAYS in DAX Commands and Tips

NETWORK DAYS

Anonymous — Thu, 18 Jan 2024 15:09:19 GMT

Hi community,

On Powerquery, I have two columns containing a start and end date, but I will like to calculate the number of working days, in a custom column. I would really appreciate any help with this.

Thank you.

Re: NETWORK DAYS

bhanu_gautam — Thu, 18 Jan 2024 16:49:55 GMT

Anonymous , Try using this method

Open the Power Query Editor.
Select the table that contains your start and end date columns.
Go to the "Add Column" tab and click on "Custom Column."
In the "Custom Column" dialog box, provide a name for the new column (e.g., "WorkingDays").
Use the following formula to calculate the working days:

M
WorkingDays = List.Difference( List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0)), List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0), #duration(1, 0, 0, -1)), (a, b) => Date.ToText(a) = Date.ToText(b) ), WorkingDaysCount = List.Count(WorkingDays)

Please accept as solution if it helps

Re: NETWORK DAYS

bhanu_gautam — Fri, 19 Jan 2024 14:41:23 GMT

, Try using this method

Open the Power Query Editor.
Select the table that contains your start and end date columns.
Go to the "Add Column" tab and click on "Custom Column."
In the "Custom Column" dialog box, provide a name for the new column (e.g., "WorkingDays").
Use the following formula to calculate the working days:

M
WorkingDays = List.Difference( List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0)), List.Dates([StartDate], Duration.From([EndDate] - [StartDate]) + #duration(1, 0, 0, 0), #duration(1, 0, 0, -1)), (a, b) => Date.ToText(a) = Date.ToText(b) ), WorkingDaysCount = List.Count(WorkingDays)

Please accept as solution if it helps

Anonymous