How to add holidays and calculate remaining working days.

To add holidays to our Date Calendar we will need a calendar that contains the holidays.

1.     After acquiring the Holidays calendar, add it to the Power BI model and connect the date column with the Date table.

2.     After connecting to the date table, we will create a new column on our main Date table and add the holidays to the new column by using a formula like this:

Holidays Text = RELATED(‘Z Holidays'[Holiday])

3.     Now that we have brought out the holidays into our date table, we will create a new column which will mark all holidays as 1 by using this formula:

Holidays = IF(‘Date'[Holidays Text] = BLANK(), 0, 1)

4.     We will also create a column on our date table that marks all Sundays as 1 using this formula:

Sundays = IF(‘Date'[Day_Name] = “Sunday”, 1,0)

5.     Now we will create a new column which will mark all the working days as 1.

Working day = IF(‘Date'[Sundays] = 1, 0, IF([Holidays] = 1, 0, 1))

6.     Now that we have created all the required columns, we will create a new measure that counts all the working days of each month using this formula:

Working Days This Month = CALCULATE(SUM(‘Date'[Working day]),DATESMTD(ENDOFMONTH(‘Date'[Date])))

7.     Next, we will create a measure that counts all the working days that have passed:

Working days passed = CALCULATE(SUM(‘Date'[Working day]),DATESMTD(‘Date'[Date]))

8.     Now that we have the measures we need we can just substitute the working days passed by working days this month:

Remaining Working Days = [Working Days This Month][Working days passed]

9.     Now we can use the remaining working days column to count the remaining working days properly, I would suggest that you have the last day of the month set on the slicer as end date if you have a date table that goes far into the future, if not I would modify the currently existing date table that only uses TODAY() as the end of the date table and replace the end date value of date table with this formula to make the remaining days measure work without any issues:

End of current month =
EOMONTH ( TODAY () , 0 )

Scroll to Top