List dates between Start & End date excluding weekends

[vikas_patel81]

Hello,

I have a dataset of construction schedule (more than 3400 tasks), which have all the information task wise (start/end dates, total resources/workforce numbers allocated for that task, etc.). My project have saturday & sunday as holiday. 

I've calculated average workforce per day using DAX below:

Planned Workforce per Day =

DIVIDE (

'visilean For Workforce'[totalPlannedWorkers],

'visilean For Workforce'[Planned Duration])

After that in Power query, I inserted this(below) as custom column to list the dates between task's planned start & finish date:

{Number.From([plannedStart])..Number.From([plannedEnd])}

(expanded to new rows after this step)

& with this I am now able to prepare report that shows daywise workforce details, but in a way - wrong. As the list shows the dates which have sundays and saturdays as well. 

Is there anyway, I can exclude weekend on this calculation?

It will be really appreciated if you can help me with this, please!!

[vikas_patel81]

This worked. Thanks so much for your input!! 

[edhans]

Great @vikas_patel81 - glad I was able to assist!

[edhans · Accepted solution]

You can approach this two ways. In another column, add this formula:

Date.DayOfWeek([Date], Day.Saturday)

That will mark Saturday as 0, Sunday as 1, and Mon-Fri as 2-6. Now, either filter out days < 2, or add "<2" to the end, that will return True for weekends and False for weekdays.

Now filter that out, or using DAX, you can count the days excluding the TRUE values for weekends.