Well done, sir. While I think it’s going to be extremely difficult to top the elegance and efficiency of your first solution, this is a very clever approach as well.
I am now creating the “Rajesh Rule” for this initiative – random drawing selectee or not, submitting three separate excellent solutions earns you a laptop sticker regardless. 
Thanks a lot, @BrianJ for this challenge. Really appreciate it.
So, here goes my solution for this week:
Some of the features that my solution includes are as follows:
I would love to see how others arrive at the solution. It’s a great initiative to unlock the power of DAX & M. 
Once the submission date is over, I am going to post about this amazing new initiative of EDNA & my Week-01 solution on my LinkedIn & Twitter handles, as well. 
Thanks, @sam.mckay & @EnterpriseDNA for making this challenge a reality.
Looking forward to more such DAX & M challenges.
Thanks @BrianJ, I completely agree, seeing other solutions once you’ve worked through it yourself will have so much more of an impact. Brilliant idea of yours and Sam’s
. Another completely different and outstanding solution - I really like the flexibility inherent in this one in its ability to handle different format input data.
Thanks also for your efforts to promote this initiative externally - really appreciate that, as we’d love to have a ton of members and non-members participating each week.
Overall, I am absolutely bowled over by the creativity and innovative thinking I’ve seen in these entries so far. I’m really learning a lot from reviewing each of them, and can’t wait for the discussion that will start in earnest right after the closing bell. I think it will be a terrific opportunity for us all to learn from each other.
Thanks for participating!
Glad that you liked the solution
The more people will engage in such challenges, the more we could learn.
Afterall its community from where we learn & grow together, thats why I wanted to let my connections on LinkedIn know about it, so that they could also join and contribute 
Excel Approach.
Excel formula to Get nth day of week in month and Get last weekday in month
Rajesh Sol4.pbix (1.4 MB)
Interesting – apparently I reinvented the wheel here, since this is remarkably similar to my solution approach, but with a better method for parsing the Day field.
All,
Huge thanks to those who already participated in our inaugural Problem of the Week. Just a reminder, if you haven’t, there’s still 24 hours left before this one closes. Note that there is not great urgency to submit before the deadline, like there is in the Data Challenges, but three things will happen after 11:59 PM ET Tuesday, 12/8:
I will post my solution both on the forum and via YouTube video, so if you’re trying to avoid seeing the solution until you work through it on your own, you’ll probably want to steer clear until you finish your solution
similarly, the forum community will begin discussing alternative approaches and other aspects of the problem. Given the vast array of different solutions already posted, I expect that to be a lively and robust discussion.
I will do the drawing for the laptop stickers for those who’ve completed solutions and want to be entered in the drawing. If you do want to be eligible for the drawing, please just drop an email to me at problemoftheweek@enterprisedna.co w/ your mailing address.
Thanks again - see you in 24 hours… 
Really excited to see the Youtube video and various approaches that our DAXstars have taken to solve the same problem.
And yes, I have dropped a mail to participate in the lucky draw too 
YouTube video posted a little earlier than expected, so it’s available now if you want to get a jump on the solution discussion. 
@BrianJ Thanks a lot !!
Thanks @Brian for coming up with a great learning initiative. Here is my solution for this challenge.
eDNA Problem of the Week 1 – Perpetual Holiday Calendar DAX- Dec 2020.pbix (1.4 MB)
Getting the solution with calculated columns was easy but I decided to come up with a Measure solution. There is no measure branching and whole lot of Var branching.
Looking forward for the next one.
Yet another interesting approach that is somewhat of a hybrid between the approach @Rajesh (in his fourth solution!) and I took, and some of the more holiday-specific approaches submitted.
Glad you enjoyed this one, and thanks a lot for participating!
All,
Well, I think we’re off to a very good start here on the inaugural Enterprise DNA Problem of the Week - over 500 views on this topic in the past week, and a dozen very different solutions submitted. A huge thanks to all who participated, whether you posted your solution or not. I really hope you found this to challenging, instructive and entertaining.
When I formulated this first problem, I had no idea that there would be as many creative ways to solve it as you all found. Here’s how I went about it…
In the YouTube solution video that I did for this problem, I discussed in some detail the general approach I take in solving these types of complex problems, focusing on the following three techniques that you can apply to almost any DAX problem:
OK, onto my specific solution. In formulating this solution, I focused on keeping it broadly applicable to any set of holiday data, and thus I broke the dates into three categories:
a) Specific Dates (e.g., January 1, December 25 of the selected year) - these were the simplest to deal with - just required harvesting the proper year, month and day values and assembling them using the DATE() function
b) Relative Dates on Specific Days (e.g., fourth Thursday in November) - my approach for these was to start with the MIN date from my Dates Table that matched the selected year, month and day, in this case starting with the first Thursday in November of the selected year, and then based on whether the occurrence was first, second, third or fourth adding 0, 7, 14 or 21 respectively to get to the proper week.
c) Relative Dates on Nonspecific Days (e.g., last Monday in May) -the approach above won’t work for these type of dates, since depending on the year there may be four or five Mondays in May. For this category, I just used the same approach above in b) used to calculate the MIN date, but instead calculated the MAX date.
In 1) above, I harvested the necessary year, month and day values
In 2) above, I broke the Holiday Day field into its two component parts, occurrence (e.g., Fourth) and Day (e.g., Thursday) using the SEARCH function to identify the position of the space between the words, and then the LEFT, MID and LEN text functions to work to the left and right of the space to capture each word individually into separate variables.
In 3) above, I calculated the MIN and MAX dates corresponding to the selected year, month and day.
In 4) above, I used a SWITCH( TRUE () ) construct to determine which category of dates (specific, relative on specific days, relative on nonspecific days) the given holiday information fell into and depending on the result of that evaluation applied the appropriate strategy to that date type as discussed above.
Once I got to that point, the Celebrated On measure was relatively easy to implement:
For the given Holiday Date, it just does a LOOKUPVALUE of the day on which that date falls, and then applies another SWITCH( ) statement to subtract a day if the day is a Saturday, add a day if the day is a Sunday, and return a blank otherwise.
Even if you took a different (and better…) approach, this hopefully provides a different and useful perspective on general strategies you can use to break down these types of problems.
While each of the solutions you posted had interesting elements definitely worthy of further discussion, there were specifically a couple that I did want to highlight here. @Rajesh’s first solution was truly a thing of beauty – 13 lines of DAX to solve the entire problem, and making perfect use of a technique I first saw from @AntrikshSharma that reduced the parsing of the text field from multiple variables in multiple lines to a single line using an ingenious application of the PATHITEM function.
While @Rajesh had the shortest solution, I also wanted to highlight the longest which was @quantumudit’s. What was really creative about this one was its flexibility in handling information that could be formatted/specified in a number of different ways. If you have a real-world application of this problem that deals with holiday data from multiple countries, I definitely recommend looking at his solution.
At this point, I wanted to open the discussion up and invite anyone to discuss their solution (at this point no summary hiding or spoiler alert structures needed), the thought process you employed to get to the answer, specific technical innovations you want to highlight, or any other element of this problem you want to talk about. In addition, I absolutely welcome feedback about this initiative in general and thoughts you have after going through the first problem.
Again thanks to all for participating - I look forward to the discussion and subsequent Problems of the Week!
P.S. Drawing results to be posted shortly. Just want to make sure my list of entrants was complete…
All,
And the winners of the Problem of the Week #1 drawing are:
@jbressan
@DianaB
@quantumudit
@Melissa
@Rajesh
The stickers came out great:
and I will mail yours off to you shortly. Thanks again for participating!
– Brian
Just want to add a few things about my solution.
If nothing is selected in the slicer, it will show holidays and celebration days for all the years in the date table.
Measure version - It is kind of optimized, 10 years of data is computed in ~140ms, 50 Years of data in ~3 Seconds, 100 Years in ~7 seconds, the best part is there are always 2 Storage engine queries that means load is on Formula Engine ( which is expected as I am using excel functions such as WEEKDAY, EOMONTH etc ), also there are no CALLBACKDATAID
Table version - If you run the code itself in DAX studio, 10 years of data is computed in ~85 ms, 50 Years in ~370ms, 100 years in 745 ms, immeasurable (2ms) when executed as a part of the visual.
Already working to see how to make it a bit better and reduce the high materialization ( 2 * Count of Years Selected * 365 ) .
Thanks – this is great. For folks who have just completed a measure and are trying to optimize it, I’m wondering if you could walk through the general process you use to evaluate the measure, and then some of the key things you focus on in trying to get it to run more efficiently? I realize this is a huge topic that we could dedicate an entire course or more to (which we plan to in the future…), but if you could just provide a broad overview, that would be fantastic.
Thanks!
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.
@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
Yes of course, be happy to
