Forum Discussion

vikas_patel81's avatar
vikas_patel81
Regular Visitor
4 years ago
Solved

List dates between Start & End date excluding weekends

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!!
 
Need Help
  • edhans's avatar
    edhans
    4 years ago

    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.

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    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.