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

[BrianJ]

OK, here we go! Problem of the Week #1 is live!

For background on this initiative, check out this post and the live launch video that Sam and I did today on YouTube.

Background on This Week’s Problem

Creating a holiday table is critical for a number of time intelligence calculations, most commonly calculating net workdays excluding holidays between two dates. In creating a holiday table, most commonly dates are pulled off the web. This works fine if the dates provided are specific, but often times (e.g., on Wikipedia) the dates provided are relative, e.g., third Monday of January, fourth Wednesday of November, etc.

Your Task

This week’s Problem of the Week challenges you to take this mix of specific and relative dates for US holidays, and create a DAX measure (or series of branched measures) that will calculate the specific Holiday Date for each holiday correctly, no matter which year is selected in the slicer.

In addition, once you’ve done that create a second measure that calculates the Celebrated On date using the following rules:

• if the Holiday Date falls on a Saturday, the Celebrated On date is the immediately previous Friday
• if the Holiday Date falls on a Sunday the Celebrated On date is the immediately subsequent Monday
• if the Holiday Date falls on a weekday, then this measure returns a blank

Note that this is a DAX-only challenge, so no Power Query even if that’s what you might choose to use outside of this challenge.

Eligibility for the Drawing

To encourage participation, we will be doing a drawing of five random participants each round to receive one of these cool Enterprise DNA vinyl laptop stickers:

To be eligible for the drawing, just send an email to problemoftheweek@enterprisedna.co before 11:59pm ET, Tuesday, December 8, 2020 indicating you’ve successfully completed the challenge, and providing your mailing address for us to send the sticker if you’re selected.

We hope you find this initiative fun, challenging and helpful in continuing to build your Power BI skills. All of the information you will need is contained in the PBIX file below. If you have any questions or comments, just message me in this forum thread @BrianJ

Good luck, and enjoy!!!

Brian

P.S. Your feedback is always welcomed, and if you have a real-world problem that you think might make a good future Problem of the Week, please email that to us at problemoftheweek@enterprisedna.co. Thanks!

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

[Rajesh]

Hi @BrianJ

Very Interesting. Attached my solution.

Summary

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

[BrianJ]

@Rajesh,

Beautifully done - a really clever and efficient solution. Totally different than the approach I took, and I actually like yours better.

• Brian

[jbressan]

[details=“Summary”]

Attached my solution.

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

[AntrikshSharma]

Here is my submission, it includes both table and measure version, my solution is more Excel oriented than DAX.

Summary

POW Antriksh.pbix (122.6 KB)

[BrianJ]

@jbressan,

Welcome to the forum – great to have you here! And kudos for diving into this community activity on your first forum post.

Nicely done, and another interesting approach taken. One suggestion - you may want to work on refining your measures to remove the test entries, since if you were to use these measures for creating an actual holiday table that served as an input to your date table, those extra row would produce incorrect results in the IsHoliday boolean field and potentially other fields related to the Holiday table.

I also want to confirm that I got your email, and will enter you in the laptop sticker drawing.

Thanks again for participating!

• Brian

[jbressan]

Thanks BrianJ,

I followed your recommendation, I remove the test rows and update the post.

[Melissa]

This is an awesome problem to start with @BrianJ, so a big THANK YOU for putting this together.

Must admit I’m already looking forward to the next one, can hardly wait - this is so much fun!
All the best.

Summary

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

[DavieJoe]

This is a really great idea/initiative. Seeing different approaches to problems will be really valuable.

[Rajesh]

@BrianJ

Here is my 2nd Solution using Calculated Column in Dates Table.

Summary

Summary

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

[Sue]

So as a starting point, my entry is long and am sure can be improved dramatically and I haven’t got to stage 2 yet, but think I am going the wrong way. I’ve created a calculated column called Holiday in the Date table that is very very long (1.4 MB)

[BrianJ]

Problem of the Week Participants,

The response to this initiative so far has been phenomenal. Personally, I’ve been fascinated to see the wide range of approaches that have been taken to solving this problem and have already learned some cool new tricks from your solutions posted thus far.

I do have a favor to ask of those participating. Absolutely please continue to post on this thread throughout the week with solutions, questions, comments, etc. However, if you do post something directly related to a solution, please use the “Hide Details” or “Blur Spoiler” options within your post. This will prevent people who have not yet completed their solution from seeing things they might not want to see yet and have influence their solution.

To use these options, just select the text or image you want to hide, click on the gear icon on the top right at the top of your post and select either Hide Details or Blur Spoiler.

Thanks, and keep those solutions coming!

• Brian

[BrianJ]

@Sue,

FYI - your image and PBIX didn’t come through in your post.

Absolutely fine to work through questions on this with the community, but for things directly related to your specific solution, please hide those using the technique above to avoid spoilers for those still working through their own solutions.

This one is definitely challenging, and working through it in pieces the way you’re doing is a good way to go.

Thanks for participating!

• Brian

[Sue]

No probs and thanks Brian

[DianaB]

Excellent idea, My solution is more complex, but I am learning from the other solutions.

Thanks

Summary

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

[BrianJ]

@DianaB,

Another really interesting approach – these are like fingerprints, with no two alike. So far I’ve seen single measures, branched measures, calculated columns only, calculated columns and measures. Fascinating…

Thanks for participating!

• Brian

[Sue]

Hi there, done I think and looking forward to seeing how others have solved the problem. Enjoyed the challenge. Fabulous idea

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

[BrianJ]

@Sue,

Great job sticking with this one, and coming to a good, working solution. As you surmised, there are some more efficient constructs, but having worked through the problem yourself those will be a lot more meaningful to you when you see them.

Glad you enjoyed this, and thanks very much for participating!

• Brian

[Rajesh]

@BrianJ

Here is my 3rd Solution. Added holiday names to date table.

Summary

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