Remove vacation day on a table made to show who registered less time than necessary

[leonicab]

Hi everyone. I need help on the following.

I need help filtering out vacation days from a table I have. Allow me to detail below.

My goal is to show a table with the employees' names, the amount of hours they worked on each day and dates. How ever, I want to show only the days that they dind't work according to their daily work time (example: 8 hours a day is the goal but they only worked 7 or less). Therefore I need to leave network days, removing weekends, holidays and vacation days.

So I have a main table called fHours, with the following columns: User | Worked Date | Work Hours and many other columns that are not relevant to this problem, plotting only the dates with time register (if the employee didn't register any work on a day, that day doesn't appear on the table)

So I created a fCalender Hours with all the dates from 2023, merging the fHours so I have the days registered + days without any register.

On another table "fHolidays" I can say if that date is a holiday or not. Also, I created another column to sy if it is weekend or not, so that's working just fine. 

Another table "fStatus" ha ve a list of all names and "Yes" or "No" in front of them to check who works 8 or 9 hours/day, so I can say if they have enough time worked/day.

NOW THE PROBLEM HERE IS:

The other table fVacations has the columns: Name | Vacation Start Date | Vacation End Date.

I need to say that, if that person was on vacation during the days between start and end date, I should not consider as a required date to work. 

For example: this employee was on vacation from date 03/01/2023 to 05/01/2023. I need to filter out the days 03 to 05, but still show day 6 as he needed to work on day 6.

So the code for that "Vacation" is only connected to the start date. But I need to show "Vacation" from 3 to 5.

The code for the measure is currently like the following:

m.Vacation =

IF (

    MIN ( fVacation[Start Date] ) >= MIN ( 'dCalender Hours'[Date] )

        && MIN ( fVacation[Finish Date] ) <= MIN ( 'dCalender Hours'[Date] ),

    "Vacation",

    "Workday"

)

Now I'm stuck into thinking how can I solve that problem. I'm not being able to make it work so I can filter all the employees vacation dates, having only start and finish date.

Can you guys please help me?

Thank you in advance! If you need more information to help building a solution, I'd be glad to provide.

[leonicab]

Help