Working Hours Excluding Public Holidays – Power BI

This blog is a continuation of one my previous blogs where I have explained in detail about the steps to calculate working hours in Power BI.

Along with the usual non-working hours & weekends, another thing that most users want to exclude from the calculation is the Public holiday. In this blog, I have explained the additional steps required to achieve this calculation.

Before we start, make sure that you have the latest version (version after Feb 2021) of Power BI installed as some functions used in this solution may not be available in the older versions.

Solution: Download preferred public holiday list & import it in Power BI. For this demo, I have downloaded the holiday list from here: https://www.calendarpedia.com/holidays/federal-holidays-2020.html

Federal Holiday =

var holidays = CALCULATE(VALUES(‘Federal holidays 2020′[Holiday]),

RELATEDTABLE(‘Federal holidays 2020’))

return

IF(holidays = “Yes”, “Yes”, “No”)

——————————————————————————–

Finally, create the column to get the hours including public holidays. The only major change in this code is inclusion of the new column from the Public (Federal) holiday table.

Hours =

//Enter the columns for Start & End dates
var start_date = ‘New Method'[New Start Date]
var end_date = ‘New Method'[New End Date]

//Date Table

var Hours_List = SELECTCOLUMNS(GENERATESERIES((8), (16)), “Hour”, [Value])
var Minutes_List = SELECTCOLUMNS(GENERATESERIES((0), (59)), “Minute”, [Value])

var two_years_ago_start = DATE(YEAR(NOW())-2,1,1)
var one_year_later_end = DATE(YEAR(NOW())+1,12,31)\
var Dates_List_stage = CALENDAR(two_years_ago_start,one_year_later_end)
var Dates_List = SELECTCOLUMNS(Dates_List_stage,”vdate”,[Date])

var HoursandMinutes = ADDCOLUMNS(

                         CROSSJOIN(Hours_List, Minutes_List),

                                “Time”, TIME([Hour], [Minute], 00),

                                “Validity”, IF([Hour]<8 || [Hour]>16,”Non working”,”Working”)

                                )

var DateTimeTable_stage = ADDCOLUMNS(CROSSJOIN(HoursandMinutes,Dates_List),

                           “weekday”, WEEKDAY([vdate],2),

                            “New combined date”,CONVERT(CONCATENATE([vdate],CONCATENATE(” “,[Time])),DATETIME)

                            )

var DateTimeTable =

SUMMARIZE(DateTimeTable_stage,[vdate],[New combined date],[Validity],[weekday],

“PublicHolidayA”,      

   CALCULATE(MAX(‘Date Table'[Federal Holiday]),     //Column that has Publicholiday

category (Yes,No)

            FILTER(‘Date Table’,                        //Table name which has holidays

info

                ‘Date Table'[Date] = [vdate])))          //Date column of Public holiday

table

//Hours Calculation

var minutesfromstart = COUNTROWS(FILTER(DateTimeTable,start_date>=[New combined date] && [Validity]=”Working” && [weekday]<6 && [PublicHolidayA] = “No”))
var minutesfromend = COUNTROWS(FILTER(DateTimeTable,end_date>=[New combined date] && [Validity]=”Working” && [Weekday]<6 && [PublicHolidayA] = “No”))

var minutes = minutesfromend-minutesfromstart

var hours = DIVIDE(minutes,60)

return

hours

——————————————————————————————

Karan Nair
Team Lead – Data Analytics
Addend Analytics