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’))
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.
//Enter the columns for Start & End dates
var start_date = ‘New Method'[New Start Date]
var end_date = ‘New Method'[New End Date]
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(
“Time”, TIME([Hour], [Minute], 00),
“Validity”, IF([Hour]<8 || [Hour]>16,”Non working”,”Working”)
var DateTimeTable_stage = ADDCOLUMNS(CROSSJOIN(HoursandMinutes,Dates_List),
“New combined date”,CONVERT(CONCATENATE([vdate],CONCATENATE(” “,[Time])),DATETIME)
var DateTimeTable =
SUMMARIZE(DateTimeTable_stage,[vdate],[New combined date],[Validity],[weekday],
CALCULATE(MAX(‘Date Table'[Federal Holiday]), //Column that has Publicholiday
FILTER(‘Date Table’, //Table name which has holidays
‘Date Table'[Date] = [vdate]))) //Date column of Public holiday
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)
Team Lead – Data 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 email@example.com or Contact us.