calculate how many holidays employee should taken since offboarding date

[annie_liu]

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#M78058

[tamerj1]

@annie_liu 
Please try

Hours Holiday Should Have been Taken =
VAR StartDate =
    SELECTEDVALUE ( Employee[Onboard Start date] )
VAR EndDate =
    SELECTEDVALUE ( Employee[Offboard End date] )
VAR DutyDates =
    CALENDAR ( StartDate, EndDate )
VAR OffDutyDates =
    FILTER ( 'Holiday', NOT ( 'Holiday'[Date] IN DutyDates ) )
RETURN
    COUNTROWS ( OffDutyDates )

[annie_liu]

@tamerji

It works to certain point in which it count the number of holiday table saying there is 13 holiday( which is wrong since it also count two holiday twice since it falls on a weekend and the header for some reason.

 it also did not take in the take in  the onboard and offboard fields ( wondering cause it's in text format?)

Ex: in which if they onboarded date of last year and offboard after 1/21/2022 they should only taken that 1 holiday.

and if they are offboarding on 12/31/2022 they should have only taken 7 holiday since we still have Vet day,Thanksgiving, and christmas from today 10/13/2022.

Holiday days Taken = VAR StartDate =

    SELECTEDVALUE ( 'New time_card_daily_2022 for Test'[OnBoardText] )

var EndDate =

    SELECTEDVALUE ( 'New time_card_daily_2022 for Test'[OffBoardText] )

VAR DutyDates =

    CALENDAR(StartDate, EndDate )

VAR OffDutyDates =

    FILTER ( 'Holiday 2022',NOT( 'Holiday 2022'[Date] IN DutyDates ) )

 RETURN

   COUNTROWS( OffDutyDates)

[v-yueyunzh-msft]

Hi, @annie_liu 

According to your description, you want to calcualte the holiday days between [OnBoardText] and [OffBoardText] and calculate it just in 2022. Right?

Here are the steps you can refer to :

(1)This is my test data :

(2)I create a 'Calendar' table like yours :

Calendar = CALENDAR( DATE(2022,1,1) , DATE( 2022,12,31))

We need to create two columns in thsi table:

Week Day = WEEKDAY(([Date]),2)

is_holiday = var _holiday = LOOKUPVALUE( 'Holiday 2022'[Date] , 'Holiday 2022'[Date], 'Calendar'[Date])
var _is_week = 'Calendar'[Week Day] in {6,7}
return
IF( _holiday || _is_week ,1,0)

(3)Then we can create a calculated column in 'New time_card_daily_2022 for Te' table :

Holiday_days = 
var _start_date = IF( [OnBoardText] > MIN('Calendar'[Date]) ,[OnBoardText], MIN('Calendar'[Date]))
var _t= FILTER('Calendar','Calendar'[Date] >= _start_date && 'Calendar'[Date] <= [OffBoardText])
return
SUMX(_t,[is_holiday])

(4)Then we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[annie_liu]

Hello Thanks for helping  but it looks like it also counted Weekend as a holiday via the Calender setting and even counted the holidays that haven't happen yet for this year 2022

Here's the sample datas:

Holiday 2022

Calendar

New Time_card_daily_2022 for test

This is the timesheet for each day of the 2022 year for each employee up to 9/21/2022

Here's what I want to see..

And this is what I see when I applied what you suggested and it's wrong since first line entry should be 1 day assuming employee was hired in the previous years since OnboardDate is blank 

[v-yueyunzh-msft · Accepted solution]

Hi , @annie_liu 

According to your description, you just need to calucate the count of holidays in your 'Holidays' table. Right?

Here are the steps you can refer to :
(1)We can update the [is_holiday] in our 'Calendar' table. And we need to configure the column type to the 'Whole Number'

is_holiday = LOOKUPVALUE('Holiday 2022'[Holiday Count] , 'Holiday 2022'[Date] , 'Calendar'[Date])

(2)Then we can calculate the count of holidays:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[annie_liu]

How is is this formula written if it's in DAX if I want to use Power BI query?

[tamerj1]

Hi @annie_liu 

how does your employee table look like? Is it connected to the date table? How does your report look like?

[annie_liu]

@tamerj1 sorry let me rephase - The report information is coming from only 1 table which is the Employee table  that has the employee ID, Name, Onboard Start date and Offboard End date and other misc fields.