Calculate Business hours by countries with different holidays

[Gabiiiii]

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())

[amitchandak]

@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/

[Gabiiiii]

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

[amitchandak]

@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))

)

[Gabiiiii]

Hi amitchandak,

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