Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Regards,
Daniel He
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |