Problem of the Week #1 (DAX) - Perpetual Holiday Calendar

[AntrikshSharma]

I can only suggest to test everything you write and see what kind of XMSQL code is generated, try to reduce the number of CALCULATE calls ( My own experience, can’t guarantee it will work for you ), think of the engines as if you are working in a bakery… it is up to you… do you want to make 10 rounds and collect 10 items or do you want to take the list with you and make only 1 trip?

Optimization is a vast topic, you should know at least 3-4 ways to write the same logic and in 1 of those ways you might be able to remove the CALLBACKDATAID, reduce materialization and Storage Engine queries.

Play with the code, keep adding or removing functions and see how it impacts your code.

[BrianJ]

@Rajesh, @quantumudit, @Sue, @MudassirAli, @Melissa, @DianaB, @jbressan,

Would you be willing to briefly walk through the elements of your submission(s), including the technical approach used and/or the general thought process you went through in getting to your solution?

Thanks!

– Brian

[Sue]

Yes of course, be happy to

[Melissa]

Hi all,

In the beginning I took a pretty similar route as @BrianJ did…
The key, separating specific- from relative holiday dates.

1. Retrieving all building blocks/elements
2. Assigning a numeric value to count words
3. Creating a list of all dates that match: Year, Month and Day name
   To be honest it’s not a list at all but a table containing 4 or 5 rows for each relative holiday
4. If the holiday has a specific date (like New Years) then construct a date value
   otherwise return a BLANK
5. For relative holidays check if we’re looking for the Last occurrence, if so
   get the first date value from the ListDates in decending order else
   get the n-Occurrence from the ListDates in ascending order
6. Finally if the result form (4) returns a blank, get the result from (5)

.

For the celebrated on date a simple SWITCH based on the Day of the Week for the Holiday date and then offsetting the Holiday date value by either minus or plus 1.

.

I find it really interesting to see so many different approaches to the same “Problem”.
Loved seeing the PATHITEM being applied here, I’ll be looking for ways to use that

Big thanks to all who participated - even if you were unsuccessful on your first attempt or did not submit, you can evaluate your thought process and/or problem break down and compare it with all solutions that are/will be posted here.

Last but not least I really enjoyed the first POTW - thanks again Brian for making this happen !!!

[BrianJ]

@Melissa,

Thanks – great explanation. Really interesting to see that our solutions were very similar up until the point when you took the TOPN route and I went with the SWITCH( TRUE() ) MIN/+7/+14/+21/MAX approach. Another illustration of how versatile DAX is.

Really glad to hear you enjoyed this first round – I’ve had a lot of fun developing and implementing it.

– Brian

[Sue]

Hi all,
I went down the calculated column route basing my solution of replicating how I normally use a Holiday Table in Power Query and pull in to the Date Table to enable filtering by that column. Definitely not the most efficient method but it worked for my brain as a starting point.

I could see that there was a pattern in the dates with some being fixed, eg, 25th December and some being relative and would therefore need to be treated differently.

I used Filter as an iterator to run through each row of the table using Var a to specify the row to consider. I then used conditions in the filter specified to check whether the current row met the criteria.

So for Labor day, the months = “September” and using Day[Date] to be less than 8 this would be the first week. Weekday[Date] allowed me to specify the day of the week.

For those dates with a fixed day, eg, Christmas I could specify the day using the Day([Date]) to equal a certain number such as 25.

This method works for first, second, third and fourth week but not last. Memorial Day is the last week in May which has 31 Days so I used that as a variable and changed the code for Day Date to be between 31 - 7 and 31.

To create the column to offset the holiday if on a weekend, I used Dates Day of Week and if a 6 then go back a day by using Date - 1, if a 7 then go forward.

Off Set =
IF(NOT(ISBLANK(Dates[Holiday])),
IF(‘Dates’[DayOfWeek] = 6, ‘Dates’[Date] -1,
IF(‘Dates’[DayOfWeek] = 0 , ‘Dates’[Date] + 1)))

I’ve attached my pbix and you’ll see that I’ve been through Melissa’s solution as a measure and commented everything as I wanted to understand her solution. I haven’t used a lot of DAX for text extraction so this was really interesting.

eDNA Problem of the Week 1 – Perpetual Holiday Calendar DAX- Dec 2020.pbix (1.4 MB)

[Melissa]

Hi @Sue,

Kudos

Amazing. Great to see how you have taken the time to investigate and explore solutions by others.

In my discovery process I also check a function library, like the Knowledgebase for details on functions that are new to me or I don’t fully understand. I find that is a fun way to learn.

[Rajesh]

Hi All

First of all big thanks to @BrianJ. This really helps to develop our DAX and M skills.

Here the problem is Day filed values some are numbers and others text.

Its easy to get date from numeric values.

Step1 - I split the day filed into two parts using PATHITEM

Step2 - If Day name is blank means we’ve numeric value in Day filed, I passed Year, Month number and Day fields to Filter function to get Date.
OR
You can simply use date function also GetDate1 = DATE(SY, SMN, SD)

Step3 - I created table by passing Year, Month number and Day Name
Now we’ve all Monday’s(Selected day) in a selected month but we need only one.

Step4 - Using switch generated numbers for First, Second…Last
Last you can pass any number greater than 5 (Every month we’ve max 5 days)
Last you can pass 1 also but you’ve to write one more step
TOPN(1…DESC)
Step 5 - Using TOPN and MAX we can get the required date.

Celebrated on I used format function to get the day name.

[BrianJ]

@Sue,

Thanks! - terrific writeup. Really does a good job explaining your thought process. Memorial Day breaking the pattern was definitely a hurdle for most of the solutions posted (mine included), and was the reason I chose U.S. Holidays for this challenge.

I absolutely love your strategy of breaking down others’ solutions by reasearching the functions/approaches you don’t understand or weren’t very familiar with and explicitly documenting them for yourself via comments in the measures. Will be a great resource to go back to over time as a way of marking what you didn’t know at the time, that I think will become second nature to you as you continue to pursue this type of deliberate practices.

Thanks very much for taking the time to provide this info. Well done. .

• Brian

P.S. Are you using Analyst Hub? If so, it would be great for you to post your commented version of Melissa’s measure to the AH community. If not, let me know and I can do it. Thanks.

[BrianJ]

@Rajesh,

Thanks for the writeup! Such an efficient and clear solution and explanation. I definitely wanted to be able to easily access components of this solution for my future work/analyses, so I posted it to the Analyst Hub community section.

Problem the Week #1 Holiday Date Solution (@Rajesh solution #1) - Analyst Hub...

• Brian

[Sue]

No probs, am using Analyst Hub and will post the commented version. Looking forward to the Power Query challenge.

[quantumudit]

My solution is influenced by the nature of work I do on my job where mostly the data grows over time and we also have to provide some rooms to handle a variety of manual user input.

I could have thought of a quick and dirty solution just to solve the challenge (which I also do when the deadline is close and I can’t able to think of any feasible solution at that moment in time).

So, rather than hardcoding stuff; I choose to write some extra line of code to add the factor of reliability.

I developed the thought process like that and tried to restrict everything to a single measure.

You can view the code snippet here: My_DAX_Solution

However, I am amazed to see the solution by @Rajesh and the optimization techniques provided by the DAXstar @AntrikshSharma.

I really learned a lot from this challenge and excited about the next Power Query challenge.

[BrianJ]

@jbressan, @DianaB, @quantumudit, @Melissa and @Rajesh,

Sorry your laptop stickers for POTW #1 have been slow getting to you - Amazon was heavily delayed in shipping the international forever stamps I needed to send these. I have received them now though, and will be sending out the stickers first thing tomorrow.

@DianaB and @quantumudit – I still need your mailing addresses. Please email that info to me at brian.julius@enterprisedna.co.

Thanks for your patience and participation!

• Brian

[quantumudit]

Hello Brian,

I confused myself with the mailing address being my email address. I thought we will be getting a soft copy of the sticker…

But, never know it would be a hard copy sticker…

I have mailed you my address in your email ID and indeed it’s a surprise.

Thanks

[chrish]

I’m sorry that I had missed the first Problem of the week - but thought that I would take the opportunity of Christmas holidays to give it a go and review other solutions.

I found the pure DAX measures approach in Brian and other’s solution’s quite complex and instead used a calculated column in the dates table to identify the holidays to their respective days (which dealt with all of the tricky First, Third, Fourth, Last of the month parsing quite efficiently) and then a couple of simple measures to pass the Holiday Date and the Celebrated on date to the solution matrix (I adapted the matrix so that I could see holidays for all years in a single visual). This got me to a solution for the US holiday problem:

But this got me thinking about how to adapt this approach to non-US holidays - specifically UK holidays. Many of these holidays are amenable to the same approach as the US holidays (ie first Monday in May, Last Mondays in May and August), but the one that really got me thinking was how to determine Good Friday and Easter Monday which move throughout March and April depending on the date of Easter. This sucked me into 2000 years of (the still unresolved) question of how to calculate the date of Easter (for those who like that sort of thing the Wikipedia article on the Easter Computus is interesting: https://en.wikipedia.org/wiki/Computus ). In short, in the western Christian tradition, Easter Day is the first Sunday after the first full moon after 21 March.

So I created an Easter computus to find this date as a separate calculated column in the dates table. The method I used is based on the Ronald W Mallen method set out at: https://www.assa.org.au/resources/more-articles/easter-dating-method/ .

I adjusted this method to work with DAX and the columns that were already in the date table. Key steps were:

• calculating the number of days that the first full moon falls after 21 March - this runs on a 19 year cycle for the period 1900 - 2199 which is good enough for my purposes.
• calculating the number of days from the full moon to the following Sunday.

Once I had finished the Easter computus, the rest of the UK holiday calculations could be done like the US ones - one small nuance is that if a UK fixed date holiday (ie New Years Day, Christmas or Boxing Day) falls on a weekend it is always taken on the next working day - this might not be the following Monday if that day is already a holiday. This gave me the UK perpetual holiday calendar:

The code for this is in the attached PBI file

eDNA Problem of the Week 1 – ChrisH Perpetual Holiday Calendar US & UK.pbix (930.9 KB)

I would welcome any comments on or improvements to my methodology for the Easter computus. Or if there are any holiday dates in your country or faith tradition that could throw up some interesting calculation challenges, I’d love to hear them.

Happy holidays,

Chris

[BrianJ]

@chrish,

This is fantastic. When I was putting this problem together and deciding on which country’s holidays to use in the dataset, I specifically shied away from any holiday list that included Good Friday and Easter, since it was beyond the complexity I wanted in the initial problem, but the research and analysis you’ve done of this question are really interesting. Thanks for sharing!

• Brian

[JBocher]

Hi @BrianJ ,

Thank you for the challenge, it is a great opportunity to learn DAX.
Here is my solution to the problem.

Summary

eDNA Problem of the Week 1 – JBocher.pbix (1.5 MB)

[BrianJ]

@JBocher ,

Welcome to the forum – great to have you here! Blowing through half the Problems of the Week in a single morning is quite the “drop the mic” introduction – very impressive!

Your solution here looks great. When you have a chance, you may want to go through the other entries in each problem – in almost every case people took very different approaches, and there are some extremely innovative and interesting solutions in each one that I know I learned a lot from.

Also, don’t know if you have yet explored the Enterprise DNA Data Challenges, but from the look of things it seems they would be right in your wheelhouse. We just opened Data Challenge #15, which goes on until August 22.

• Brian

[JBocher]

Thank you @BrianJ for your reply.

Actually it took me a few days to complete the POTW
I work with @alexbadiu and he advises me to publish my solutions.

I started with the PBI Accelerator and it’s a good idea to continue with the Challenge.
Thank you for all the contents, it’s perfect to start learning how to use PBI as I did.

[BrianJ]

@JBocher ,

That’s wonderful that you’re already taking such great advantage of the range of membership offerings. And you couldn’t have a better advisor than @alexbadiu. He is one of the Data Challenge rock stars.

• Brian