Calculate the total holidays and businessdays based on location

[Anonymous]

Hi,

I have a very common scenario which works well in excel but gets tricky while trying to convert in Power BI.

I want to calculate the total holidays between two dates, the trick here is the holidays has to be calculated based on location, I have the screenshot of my holiday_location table and my Employee Fact table, I am unable to create relationship between these two and neither calculate the Holidays including the Weekends, Kinly help me with it.

Location_Holiday Table

Country_Name+Statename	State_Name	Country_Name	HoliDate
UK and WALES_London, UK	London, UK	UK and WALES	29-May-17
UK and WALES_London, UK	London, UK	UK and WALES	2-Apr-18
UK and WALES_London, UK	London, UK	UK and WALES	25-Dec-17
UK and WALES_London, UK	London, UK	UK and WALES	26-Dec-17
UK and WALES_London, UK	London, UK	UK and WALES	1-May-17
UK and WALES_London, UK	London, UK	UK and WALES	28-May-18
USA_New Jersey	New Jersey	USA	1-Jan-18
USA_New Jersey	New Jersey	USA	20-Feb-17
USA_New Jersey	New Jersey	USA	14-Apr-17
USA_New Jersey	New Jersey	USA	29-May-17
USA_New Jersey	New Jersey	USA	4-Jul-17
USA_New Jersey	New Jersey	USA	4-Sep-17
USA_New Jersey	New Jersey	USA	23-Nov-17

Fact Table

[v-danhe-msft]

Hi @Anonymous,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem like the picture below:

2.Create a new measure and create a Table visual and add the related columns.

Amount of holiday = CALCULATE(COUNTROWS('Location_Holiday Table'),FILTER('Location_Holiday Table',MAX('Fact Table'[Enddate])>'Location_Holiday Table'[HoliDate]+365&&MIN('Fact Table'[Startdate])<'Location_Holiday Table'[HoliDate]+365&&'Location_Holiday Table'[State_Name]=MAX('Fact Table'[Location City])))

Now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ypobpg6dniy93wy/Calculate%20the%20total%20holidays%20and%20businessdays%20based%20on%20location.pbix?dl=0

Regards,

Daniel He

[Anonymous]

Hi Daniel,

Sorry for the late reply and thanks for your effort,  I have tried your solution and it works perfectly for the given holidays, but when I try to increase the number of holiday dates it gives me blank, below is the complete list of holidays for two states.Kindly let me know if you need any inputs

State	Holidate
California	1-Jan-18
California	20-Feb-17
California	14-Apr-17
California	29-May-17
California	4-Jul-17
California	4-Sep-17
California	23-Nov-17
California	16-Jan-17
California	25-Dec-17
California	2-Jan-17
California	15-Jan-18
California	19-Feb-18
California	30-Mar-18
California	28-May-18
California	4-Jul-18
California	3-Sep-18
California	22-Nov-18
California	23-Nov-18
California	24-Nov-17
California	25-Dec-18
Colorado	1-Jan-18
Colorado	20-Feb-17
Colorado	14-Apr-17
Colorado	29-May-17
Colorado	4-Jul-17
Colorado	4-Sep-17
Colorado	23-Nov-17
Colorado	16-Jan-17
Colorado	25-Dec-17
Colorado	2-Jan-17
Colorado	15-Jan-18
Colorado	19-Feb-18
Colorado	30-Mar-18
Colorado	28-May-18
Colorado	4-Jul-18
Colorado	3-Sep-18
Colorado	22-Nov-18
Colorado	23-Nov-18
Colorado	24-Nov-17
Colorado	25-Dec-18