topic Re: Workday count between two dates in DAX Commands and Tips

Workday count between two dates

Anonymous — Tue, 29 Mar 2022 14:40:57 GMT

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!

Re: Workday count between two dates

tamerj1 — Tue, 29 Mar 2022 15:22:16 GMT

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

Re: Workday count between two dates

Anonymous — Tue, 29 Mar 2022 15:51:55 GMT

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!

Re: Workday count between two dates

tamerj1 — Tue, 29 Mar 2022 16:05:09 GMT

Anonymous

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