Forum Discussion
Calendar Table for different regions
Greetings!
I need to create an office attendance report 😢. In other words, I need to count the days the employees were in the office and compare them with the number of working days in the period.
But the offices are in different locations (cities) and there are different holidays for each city.
Example:
25/Jan is a holiday in city 1 only
29/Mar is a holiday in city 2 only
What would be the best practice in this case? Should I create two tables (dCalendarCity1 and dCalendarCity2)?
Hi Anonymous You can handle this with a single calendar table by adding a Region column to differentiate cities and a Holiday column to mark holidays for each region. Use these columns to filter working days per city in your DAX measures. Relate the calendar to your attendance data and calculate WorkingDays by excluding holidays.
5 Replies
- Akash_Varuna
Super User
Hi Anonymous You can handle this with a single calendar table by adding a Region column to differentiate cities and a Holiday column to mark holidays for each region. Use these columns to filter working days per city in your DAX measures. Relate the calendar to your attendance data and calculate WorkingDays by excluding holidays.
V-yubandi-msftCommunity Support
Hi Anonymous ,
Thank you for being part of the Microsoft Fabric Community. The solution shared by Akash_Varuna effectively aligns with your requirements.
Key concept:
Utilize a single calendar table enriched with
- A City or Region column to distinguish locations
- An IsHoliday flag to identify holidays for each city
This setup allows you to calculate working days dynamically for each city without duplicating tables, making your model clean, scalable, and easier to maintain.
Thank you Akash_Varuna for your response.
More details can be found in Microsoft documentation here: Design guidance for date tables in Power BI Desktop - Power BI | Microsoft Learn
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly
- V-yubandi-msft
Hi Anonymous ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- V-yubandi-msft
Hi Anonymous ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
- V-yubandi-msft
Hi Anonymous ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.