holiday
2 Topics
Dynamic Holiday Calendar
Sharing this solution in case anyone else finds it useful. My company closes on US Federal Holidays and some additonal dates. The difficulty with identifying this data year after year is that some of these dates are dyanmic based on the week in a month or when they are observed if falling on a weekend. Obviously the static dates are easy year after year. After a lot of piecing together and trial/error, here's the measure I created w/notes on what the date represents: isholiday = SWITCH(([Date]), DATE(YEAR([Date]), 01, 01), "Y", // New Year's Day IF(WEEKDAY(DATE(YEAR([Date]), 01, 01), 2) = 7, DATE(YEAR([Date]), 01, 02),""), "Y", // New Year's Day on Sunday, observed 02Jan IF(MONTH([Date]) = 1, CEILING(EOMONTH([Date],-1)-1,7)+16, ""), "Y", //MLK Day (3rd Monday in Jan) IF(MONTH([Date]) = 2, CEILING(EOMONTH([Date],-1)-1,7)+16, ""), "Y", //President's Day (3rd Monday in Feb) IF(MONTH([Date]) = 5, CEILING(EOMONTH([Date], 0)-1,7)-5, ""), "Y",//Memorial Day DATE(YEAR([Date]), 06, 19), "Y", //Juneteenth IF(WEEKDAY(DATE(YEAR([Date]), 06, 19), 2) = 6, DATE(YEAR([Date]), 06, 18),""), "Y", // Juneteenth on Saturday, observed 18Jun IF(WEEKDAY(DATE(YEAR([Date]), 06, 19), 2) = 7, DATE(YEAR([Date]), 06, 20),""), "Y", // Juneteenth on Sunday, observed 20Jun DATE(YEAR([Date]), 07, 04), "Y", // July 4th IF(WEEKDAY(DATE(YEAR([Date]), 07, 04), 2) = 6, DATE(YEAR([Date]), 07, 03),""), "Y", // July 4th on Saturday, observed 03Jul IF(WEEKDAY(DATE(YEAR([Date]), 07, 04), 2) = 7, DATE(YEAR([Date]), 07, 05),""), "Y", // July 4th on Sunday, observed 05Jul IF(MONTH([Date]) = 9, CEILING(EOMONTH([Date],-1)-1,7)+2, ""), "Y", //Labor Day (1st Monday in Sep) IF(MONTH([Date]) = 11, CEILING(EOMONTH([Date],-1)-4,7)+26, ""), "Y", //Thanksgiving (4th Thursday in Nov) IF(MONTH([Date]) = 11, CEILING(EOMONTH(DATETABLE[Date],-1)-4,7)+27, ""), "Y", //Day after Thanksgiving (4th Friday in Nov) DATE(YEAR([Date]), 12, 24), "Y", //Chrismas Eve DATE(YEAR([Date]), 12, 25), "Y", //Christmas Day DATE(YEAR([Date]), 12, 26), "Y", //Closed DATE(YEAR([Date]), 12, 27), "Y", //Closed DATE(YEAR([Date]), 12, 28), "Y", //Closed DATE(YEAR([Date]), 12, 29), "Y", //Closed DATE(YEAR([Date]), 12, 30), "Y", //Closed DATE(YEAR([Date]), 12, 31), "Y", //New Year's Eve "") Pre-requiste: Date table with a Date field. To assess if your date in another table is a holiday in your Date table (1:M or 1:1 relationship with additional tables), you can use this formula in a measure: IF( CALCULATE( MIN('DATETABLE'[isholiday]), FILTER('DATETABLE', [Date] = MIN([yourdatetocompare]) && [isholiday] = "Y")) = "Y", dothisiftrue, dothisiffalse) Hope someone else finds this useful and if you do - cheers!Solved1.3KViews0likes1Comment
calculate how many holidays employee should taken since offboarding date
So created a visual table that pull the information of the employee's start and end date from two tables but now I want to know how I can show the number of federal holiday an employee should have taken from their start date this year 2022 to when they offboarded. Let's say that an employee onboarded on April 2021 and offboarded on 9/30/2022..I only want to know the number of holidays in Hrs ( assuming they work 8 hrs a day) they should have taken in the 2022. then then once 2022 is over how many holidays they taken in 2023..so on and so on.. I have the holiday table set up to take in all the holiday for the next 10 years so that it can just auto populate. I have a holiday calender with holiday till 2033 and then I have calendar table for only current year with the Holiday tag from this in which is tag which days are weekends and holiday observed day of the week. https://community.powerbi.com/t5/Desktop/Public-Holiday-Script/m-p/178341#M78058Solved1.7KViews0likes8Comments