Working with Workdays in Power BI – Translating Workdays into Dates

EDIT: an updated version of this article with a video can be found here: https://businessintelligist.com/?p=2580

Power BI has a robust set of functions to write date-based calculations. One of the areas that has proven to be a challenge is related to doing math on working days instead of normal dates. \

Here is a simple scenario, let’s say I have a schedule of tasks that are supposed to be done on certain dates.

Let’s also say that I have a time off calendar, in which I specify when I am on leave and how many hours I will be taking off (so I will mark 8 hours if I take one day off, or 16 hours for two days etc.)

So, with this time off schedule, I will not be able to complete the Task ID = 15, and I will also not be able to complete my Task ID = 26 because of my time off with TimeOffID = 2 and because this time off spans two working days, it will actually spill over to the following Monday (Jan 29, 2018)

Even though this could be accomplished with a couple of calculations, I will break the process of accomplishing this down in several simple steps to make it easier to understand:

1. Add a WeekNo, WeekDay and WorkingDayIndex columns to the Date dimension
   1. WeekNo = WEEKNUM(‘date'[Date], 1)
      
   2. WeekDay = WEEKDAY(‘date'[Date], 1)
      
   3. WorkDayIndex = IF(‘date'[WeekDay]>1 && ‘date'[WeekDay]<7, (‘date'[WeekNo]-1)*5 + ‘date'[WeekDay]-1)
      

      

      (please note that this logic skips non-working days. If you have custom non-working days, then the IF logic needs to be made more robust to account for that)
      

2. Add a End Date (using working days) calculation to the Time Off table that will calculate the end date based on the working days from the duration to skip weekend dates, etc
   

   End Date (using work days) = LOOKUPVALUE(‘date'[Date], ‘date'[WorkDayIndex], LOOKUPVALUE(‘date'[WorkDayIndex], ‘date'[Date], TimeOff[Start]) + CEILING(DIVIDE(TimeOff[Duration], 8), 1))-1
   

   The reason the calc above works is because each working day has its own index or ID, so adding duration to the first day of the time off will tells us the ID of the last working day and then we just need to lookup it’s date.
   

3. Add ShouldBeExcluded flag to the Tasks table to go through each task and determine if it should be excluded based on the TimeOff schedule
   
4. ShouldBeExcluded = CALCULATE(COUNTROWS(TimeOff), FILTER(ALL(TimeOff), TimeOff[Start]<=Tasks[Date] && TimeOff[End Date (using work days)] >= Tasks[Date]))
   

You can find the Desktop file with sample data and calculations in this link.