Workday count between two dates

[Anonymous]

Hi all, 

Can you help me filter my measure by month? 

I have an employee-table with start and end-dates and a date table with a workday column where each workday is 1 and all non-workdays are zero. I'm trying to create a measure which counts the total amount of workdays within the filter context. I think I got this to work with this expression:

Sum of working days = SUMX(
    'Medewerkers', 
    CALCULATE(
        SUM(Kalender[Werkdag]),
        DATESBETWEEN(
            'Kalender'[Date],
            'Medewerkers'[Datum_in_dienst_dienstjr],
            'Medewerkers'[Einddatum_contract]))
    )

I sum the workday column with a datesbetween-filter with the end and start date, whitin a sumx for the employee-table. 

With this measure I can see the total amount of working days on indvidual levels and/or department totals etc. However, when trying to get the amount of working days per month, for a specific person, per month I'm getting this result:

Is there a way to overcome this? A filter I can apply or relationship I have to make? There is currently no relationship between the date table and the employee table.

I can't seem to make sense of it and all the examples I found online use calculated columns, which I think doesn't work since it should be dynamic; the amount of working days have to be able to be sliced by date. 

Thanks in advance!

[tamerj1 · Accepted solution]

@Anonymous 

DATESBETWEEN Removes the filter from the date table. It sepends on your columns but tou may try

Sum of working days =
SUMX (
    'Medewerkers',
    CALCULATE (
        SUM ( Kalender[Werkdag] ),
        DATESBETWEEN (
            'Kalender'[Date],
            'Medewerkers'[Datum_in_dienst_dienstjr],
            'Medewerkers'[Einddatum_contract]
        ),
        VALUES ( Kalender[month] )
    )
)

[Anonymous]

Thanks, that seems to solve it! 

Can you maybe explain why/how this works as a filter? I'm pretty new to DAX and like to learn more about how it works. 

Thanks anyhow! 

[tamerj1]

@Anonymous 

Filters are actually tables. VALUES is also a table therefore, it restores back the Month column in the filter contaxt.