Date Table: Dealing with local Holidays?

Power Query
1

Thanks to all experts invested their time into sharing M Code for the Date Table and Public Holidays scrapping.
I wonder if anyone can share their approach to the local holidays like City Day (which is an official holiday in this particular region but not in the others). How we can resolve the ambiguity for measures like number of days worked or time for delivery (excl holidays)?

Ideally, I’d like to have one measure that works well for all locations so was hoping it could be done in Date Table’s M Code.

2

Hi @StaceyL. The eDNA Extended Date Table is written in M and will include one set of holidays if that option is selected. See the top post in the “M Showcase” category.
Greg

3

Thank you, @Greg, I’ve seen these showcases. My question was about the scenario whem in fact we have a few sets of holidays.
E.g. Wellington Dat is 25th of January and Auckland Day is 29 of January, so when wellingtonians enjoy their extra holiday, aucklanders work and vice versa.

4

Hi @StaceyL. I’m not aware of solutions for multiple sets of holidays for the same report … one set per individual report yes, but multiple sets, no. I’d be happy to learn otherwise.
Greg

5

This (different holidays for different locations) is the reality at least for New Zealand and Australia. I wonder if we have local guru on this forum to share their insights.

6

I did some googling and here are my take aways (not M Code though):

  1. Create separate table with holidays by location, linked to the dates table.
  2. Add filter for location each time when calculating work days.

Links:

1 Like
7

Thank you for sharing these links and info @StaceyL :slight_smile:

We appreciate users and experts sharing varied updates and tips relevant to Power BI. We are tagging this post as Solved. For further questions related to this post, please make a new thread.