Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Workday count between two dates

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...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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] )
    )
)

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 

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