Trying to filter count of hire dates based on relative dates

[Bluestonegemini]

Hi....I have created a power pivot where I want to count the number of the beginning headcount, number of hires, number of terms, and ending headcount as of that Sunday.  From there, I will pull the same date for the four weeks prior to last week, and then YTD.  I am really struggling with how to write an formula that will take that into account.  

Date

Year

Month Number

Month

MMM-YYYY

Day Of Week Number

Day Of Week

Week Start Date

Week End Date

Previous Week Start Date

Previous Week End Date

Rolling 4 Weeks Start Date

Rolling 4 Weeks End Date

11/1/2023

2023

11

November

Nov-23

4

Wednesday

10/30/2023

11/5/2023

10/23/2023

10/29/2023

10/2/2023

10/22/2023

11/2/2023

2023

11

November

Nov-23

5

Thursday

10/30/2023

11/5/2023

10/23/2023

10/29/2023

10/2/2023

10/22/2023

11/3/2023

2023

11

November

Nov-23

6

Friday

10/30/2023

11/5/2023

10/23/2023

10/29/2023

10/2/2023

10/22/2023

11/4/2023

2023

11

November

Nov-23

7

Saturday

10/30/2023

11/5/2023

10/23/2023

10/29/2023

10/2/2023

10/22/2023

11/5/2023

2023

11

November

Nov-23

1

Sunday

10/30/2023

11/5/2023

10/23/2023

10/29/2023

10/2/2023

10/22/2023

11/6/2023

2023

11

November

Nov-23

2

Monday

11/6/2023

11/12/2023

10/30/2023

11/5/2023

10/9/2023

10/29/2023

11/7/2023

2023

11

November

Nov-23

3

Tuesday

11/6/2023

11/12/2023

10/30/2023

11/5/2023

10/9/2023

10/29/2023

I have created a calendar table that calculates the previous week start and end date based on the calendar date.  But when I try to create the below measure, I get an error saying there are too many values.  Any help would be appreciated!

=CALCULATE(COUNT(EmployeeTable[Hire/Rehire Date]),FILTER('EmployeeTable'[Hire/Rehire Date],'EmployeeTable'[Hire/Rehire Date]>=MIN('Calendar'[Previous Week Start Date])
&& 'EmployeeTable'[Hire/Rehire Date] <= MAX('Calendar'[Previous Week End Date])))

[tackytechtom]

Hi @Bluestonegemini ,

Two quick questions:

1) Is there a relationship in place from your date column in EmployeeTable and the calendar table?

2) Does it work if you remove the MIN and MAX function in the filter part?

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/