OK, here we go! Problem of the Week #1 is live!
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.
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 firstname.lastname@example.org 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!!!
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 email@example.com. Thanks!