Filters wrongly changing result in measure

[Banistas]

I have a measure that works well up until I add filters to it. However, I don't understand why the filters affect the measure the way they do and I need help figuring it out.  

The measure is "FTE to Pay":

FTE to Pay =
DIVIDE ( SUM ( 'Schedule'[Total Scheduled Hours] ), [No. of Week Days] * 8 )
-- The measure calculates the FTE from the total hours 

However, when I add a Calculate with filters the result changes under some scenarios (and it's wrong): 

FTE to Pay (with filters) = 
     CALCULATE( 
         DIVIDE( sum('Schedule'[Total Scheduled Hours]), 
                 [No. of Week Days]*8 
         ), 
      FILTER('Schedule','Schedule'[Shift Date] > DATE(2024,02,01)), -- does not calculate for data older than February 1st, 2024 
      FILTER('Schedule','Schedule'[Termindated?] <> "Terminated") -- stops calculating when the employee has been terminated ) 

-- The measure intends to calculate the FTE from the total hours 

Note that:

No. of week days =
CALCULATE ( COUNTA ( date[week_day_flag] ), date[week_day_flag] = "Y" )
-- The measure counts the number of week days (Monday to Friday) as per column week_day_flag 

Also, tables 'Date' and 'Schedule' are linked on Calendar Date in 'Date' and Shift Date in 'Schedule'.

One of the scenarios I have identified where the result is wrong is:

The employee works the following schedule:

Monday - 8 hours

Tuesday - Day off

Wednesday - 8 hours

Thursday - 8 hours

Friday - 8 hours

Saturday - 8 hours

Sunday - day off  

So, the employee is working 40 hours a week, and on a 8-day standard full-time, I should get a result of 1.0, even though the employee is off on a weekday and working instead on Saturday. The measure results are as follows:

FTE to Pay = 1.0   -- correct  

FTE to Pay (with filters) = 1.25   -- wrong

I believe the reason why it gives 1.25, is that it only counts 4 weekdays on the calculation with filters instead of 5 days. However, I don't understand why it does that when the filters are not indicating that No. of Week Days should be filtered. 

Any help would be greatly appreciated.     

[Banistas · Accepted solution]

Thanks @lbendlin. I resolved it by adding the filters to the table in Power Query rather than having the filters in the measure.   

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...