Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Drewdel
Advocate II
Advocate II

Problem Calculating Holidays in Date Dimension Calendar

Hi, I'm new to the Power BI community but not so new to Power BI.  I got my first job in the middle of February as a Business Analyst using Power BI and love what I'm doing since I caught it pretty quickly; probably within the first two weeks.
Anyway, other than work I've been playing around in Power BI in my spare time and started building my own date dimension calendar but am running into a problem when it comes to calculating the holidays.  When it comes to holidays that are like third Monday or second Wednesday in a month, I use the RANKX function (I found this formula online but don't remember where though) and just to find Nth day of a month and just match it to get the holiday.  It's probably a little over convoluted but it's all I've got right now.  My main problem however, is I don't know how to calculate a holiday that goes to the next month like Cyber Monday, since it immediately comes after Thanksgiving but can be in the next month if Thanksgiving falls in the fifth/last week of the November.  I'd appreciate any help on this and if anyone has a simpler way of calculating things I'd love to hear it as well.
Thanks.

 

Here's a snippet of my code but my file contains everything:

SWITCH(
   TRUE()
   ,DateDim[MonthNo] = 1 && DateDim[MonthDayNo] = 1 ,"New Year's Day"
   ...
   ,DateDim[MonthNo] = 11
      && IF(
         DateDim[WeekdayFull] = "Friday"
         ,RANKX(
            FILTER(
               DateDim
               ,DateDim[WeekdayFull] = "Friday"
                  && DateDim[MonthNo] = EARLIER(DateDim[MonthNo])
                  && DateDim[YearFull] = EARLIER(DateDim[YearFull])
            )
            ,DateDim[Date]
            ,,ASC
         )
         --,BLANK()
      ) = 4 ,"Black Friday"
   ,DateDim[MonthNo] = 11
      && IF(
         DateDim[WeekdayFull] = "Monday"
         ,RANKX(
            FILTER(
               DateDim
               ,DateDim[WeekdayFull] = "Monday"
                  && DateDim[MonthNo] = EARLIER(DateDim[MonthNo])
                  && DateDim[YearFull] = EARLIER(DateDim[YearFull])
            )
            ,DateDim[Date]
            ,,ASC
         )
         --,BLANK()
   ) = 5 ,"Cyber Monday"
   ...
   ,DateDim[MonthNo] = 12 && DateDim[Date] = ENDOFMONTH(DateDim[Date]) ,"New Year's Eve"
   ,"Workday" --,BLANK()
)

 

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Drewdel,

 

You may just add 4 days to Thanksgiving holiday.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yeah, I get that but how would I actually do that with how I have my code set up?  If I can't with the code I'm currently using, then I'm sure there's another, more simple way of calculating holidays, though I haven't figured it out yet because I've been busy.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.