Calculating amount of working hours in another table and define the filter on the result

[Thomas_D]

Hi Community,
I spent already a lot of time to resolve this, but I don't find the right approach for that.

Task:

I have to calculate the headcount of our company corresponding to the date range which is selected in the report by a slicer.

Situation:
I have a table let's call it "Employee" which have two relevant dates. The start date and the end date of the emploment. 
Additional  I have already a calendar table which is used for the filtering of the dates.  
What already works correctly, is to calculate the employees based on the start and end date.

What I need now is an additional filter, so that "external" employess are will take into account only if they have worked more than 20 hours in a month.  Therefore we have a table with the data of the working time measurement.  
I trired some different approaches but I didn't find the correct one yet.  I didn't manage it to calcualte the monthly working time for the Externals in the right way. 

Here is my measure what works in the case of taking the employment dates into account

MAH Extern = CALCULATE( COUNTROWS(Employee)),

FILTER (Employee, Employee[Int/Ext] = "extern")

,FILTER (Employee, Employee[Startdate] <= MAX(Calendar[Date]))

,FILTER ('Employee', ISBLANK(Employee[Enddate]) || Employee[Enddate] > MIN(Calendar[Date])

))

e.g. i've tried this approach, but there is someting with the filter context not correct as it returns the exact same numbers as the measure above:

MAHeadcountExternValidWithWork_NEW2 =

MAH Extern = CALCULATE( COUNTROWS(Employee)),

FILTER (Employee, Employee[Int/Ext] = "extern")

,FILTER (Employee, Employee[Startdate] <= MAX(Calendar[Date]))

,FILTER (Employee, ISBLANK(Employee[Enddate]) || Employee[Enddate] > MIN(Calendar[Date])

,FILTER (WorkingHours, SUM(WorkingHours[Hours]) > 20))

))

How do I need to sum the monthly working houres in this context correctly.

Thanks
Thomas

[Thomas_D]

Thanks for that which solved my first problem.
I've an additional problem which is caused in any way by the filter context. I tried some different approaches to use different filter criterias but I could not find the right solution.

As described above for my first measure the start date for an employment is relevant. 
I use the date column from the calendar table in the data model. On the report I have a date slicer which defines the start and end date of the relevant time period.
In the first step I only want to use the start date of an employment. When I create a measure where the date is hare coded everything looks fine. But when I use the min or max date out of the date column of my calendar table something goes wrong.

In the following screen shot you see two cards with the values I get when I use Min(Calendar[Date]) Max(Calendar[Date]). 
In the table you see two measures. The left one (where date is hard coded) counts correctly, the right column is wrong.

Here is the syntax of the two measures:

Measure 1:

MAH Extern EXT und Eintritt FILTER HARD = 
var Result = 
    CALCULATE(
        COUNTROWS('Mitarbeiter (BlueAnt)'),
        FILTER('Mitarbeiter (BlueAnt)', 'Mitarbeiter (BlueAnt)'[Int/Ext] = "extern")
        ,FILTER(ALL('Mitarbeiter (BlueAnt)'), 'Mitarbeiter (BlueAnt)'[Eintrittsdatum] <= DATE(2020,01,31))
        )
return Result

Measure 2:

MAH Extern EXT und Eintritt FILTER CAL = 
var MinDate = MIN(Kalender[Datum])
var MaxDate = MAX(Kalender[Datum])

var Result = 
    CALCULATE(
        COUNTROWS('Mitarbeiter (BlueAnt)'),
        FILTER('Mitarbeiter (BlueAnt)', 'Mitarbeiter (BlueAnt)'[Int/Ext] = "extern")
        ,FILTER('Mitarbeiter (BlueAnt)', 'Mitarbeiter (BlueAnt)'[Eintrittsdatum] <= MaxDate)
        )
return Result

Within the data model, the calendar table has a relation to the table with the saved working hours. The working hour table has a realtion to my employee table. 

What did I miss? 

[Anonymous]

MAH Extern =
var MinimumDate = MIN( Calendar[Date] )
var MaximumDate = MAX( Calendar[Date] )
var Result =
    CALCULATE(
        COUNTROWS( Employee ),
        KEEPFILTERS(
            Employee[Int/Ext] = "extern"
        ),
        KEEPFILTERS(
            Employee[Startdate] <= MaximumDate
        ),
        KEEPFILTERS(
            OR(
                ISBLANK( Employee[Enddate] ),
                Employee[Enddate] > MinimumDate
            )
        )
    )
return
    Result


// 2nd measure (can be hidden if you don't need it)
[Total Working Hours] = SUM( WorkingHours[Hours] )


MAHeadcountExternValidWithWork =
CALCULATE(  
    [MAX Extern],
    FILTER(
        DISTINCT( Employee[EmployeeID] ),
        [Total Working Hours] > 20
    )
)