topic Re: Calculate Business hours by countries with different holidays in DAX Commands and Tips

Calculate Business hours by countries with different holidays

Gabiiiii — Fri, 01 Jul 2022 06:04:01 GMT

Hi Power BI Community,

I'm replacing all the calender days calculation to business days:

Exclude weekends, exclude holidays in different countries (have a holiday table to store holiday dates for different countries)

I have done some research in the forum, but was still not working.

Basically, I used the method of calculating working hours to get the business hours besides weekends. and then subtract the number of holidays if it happens to be a weekday.

However, an error occured as this: A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported.

Here is the DAX I currently use:

businessday = 

var _END =Fact[completedon]

var _START =Fact[createdon]

var _holiday =

 CALCULATE (

 SUM (Holidays[Isweekday]),

 DATESBETWEEN (Holidays[holidaydate],_START,_END),

 Holidays[countrycode]=EARLIER(Fact[countrycode])

 )

var _workinghours = 

SUMX(

 CALCULATETABLE(

 Dates,

 DATESBETWEEN(Dates[Date],_START,_END),

 Dates[IsWorkingDay] = TRUE()),

 MAX(MIN(Dates[End],_END)-MAX(Dates[Start],_START),0)

 )

return IF(_workinghours-_holiday>0,_workinghours-_holiday,BLANK())

Re: Calculate Business hours by countries with different holidays

amitchandak — Thu, 30 Jun 2022 02:04:38 GMT

Gabiiiii , If you need

Business Days

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

If you need business hours check solution from Matt

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

Re: Calculate Business hours by countries with different holidays

Gabiiiii — Thu, 30 Jun 2022 03:34:12 GMT

Thank you amitchandak, the link you share is very helpful. However, I still cannot figure out how to calculate the business days by different countries as they have different holidays.

I change my code to this, it would return the wrong value.

TAT = 
var _END =Table[completedon]

var _START =Table[createdon]

var _totalday = GENERATESERIES(_START,IF(ISBLANK(_END),0,_END))

var _1 = GROUPBY(FILTER(Holidays,Holidays[businessunit]=EARLIER(Fact[businessunit])),Holidays[crc5e_holidaydate])

var _2 = EXCEPT(_totalday,_1) 

var _a = ADDCOLUMNS(_2,"weeknum",WEEKDAY([Value],2))

var _workinghours = 

SUMX(

 FILTER(_a,[weeknum]<6),

 MAX(MIN(hsbc_CLTask[clwft_completedon].[Date],_END)-MAX(hsbc_CLTask[createdon].[Date],_START),0)

 )

return _workinghours

Re: Calculate Business hours by countries with different holidays

amitchandak — Thu, 30 Jun 2022 05:44:22 GMT

Gabiiiii , Assume it is only weekend , you do not need a holiday calendar

Work Day =

Switch( True() ,

[Country] in {"USA", "UK", "IN", "AUS"} ,

COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)),

[Country] in {"UAE", "SA"} , // assume having holiday on Friday and Saturday

COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) in {5,6} ,0,1)),[WorkDay] =1)),

COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

)

Re: Calculate Business hours by countries with different holidays

Gabiiiii — Fri, 01 Jul 2022 06:05:33 GMT

Hi amitchandak,

Things still didn't work out. Please check my updated post. Thank you!