Forum Discussion
Solved
Distinct Count based on two date columns
Hi all, I am trying to retrieve a value from a table that has certain conditions (between two dates), but I got lost on my DAX. It would be great if one of you was able to help. My Employee Ta...
Hi frankyjones
please try
Number of employees =
VAR MaxDate =
MAX ( 'Calender'[DATE] )
VAR MinDate =
MIN ( 'Calender'[DATE] )
RETURN
COUNTROWS (
FILTER (
VALUES ( 'Dim Employees'[EMPLOYEE_ID] ),
NOT ISEMPTY (
FILTER (
CALCULATETABLE ( 'Dim Employees' ),
'Dim Employees'[CONTRACT_STARTDATE] <= MaxDate
&& 'Dim Employees'[CONTRACT_ENDDATE] >= MinDate
)
)
)
)
Hi frankyjones
please try
Number of employees =
VAR MaxDate =
MAX ( 'Calender'[DATE] )
VAR MinDate =
MIN ( 'Calender'[DATE] )
RETURN
COUNTROWS (
FILTER (
VALUES ( 'Dim Employees'[EMPLOYEE_ID] ),
NOT ISEMPTY (
FILTER (
CALCULATETABLE ( 'Dim Employees' ),
'Dim Employees'[CONTRACT_STARTDATE] <= MaxDate
&& 'Dim Employees'[CONTRACT_ENDDATE] >= MinDate
)
)
)
)