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.
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() )
You may just add 4 days to Thanksgiving holiday.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |