Forum Discussion
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
- tamerj1Community 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] ) ) )- AnonymousNot 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!
- tamerj1Community Champion
Anonymous
Filters are actually tables. VALUES is also a table therefore, it restores back the Month column in the filter contaxt.