Working Hours Excluding Public Holidays – Power BI

  • Post category:General
  • Post author:

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

For ease of calculation, I have added a ‘Holiday’ column with value ‘Yes’
 
2)     Create relationship between the new table & existing date table. Next, create a calculated column in the date table to bring the public holiday information. Use the code below:
 
——————————————————————————–

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.

Here’s the full code:
———————————————————————————

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

Addend Analytics is a Microsoft Power BI-partner based in Mumbai, India. Apart from being authorized for Power BI implementations, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com or Contact us.