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 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!

 

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

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    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

      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

        Anonymous 

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