Forum Discussion
Distinct count with datediff between column date and selected date value
Community SupportHi Anonymous ,
According to your description, two additional filters need to be added to exclude weekend dates. Refer to a similar test.
employee_count_90days =
CALCULATE (
DISTINCTCOUNT ( employees[employee#] ),
FILTER (
employees,
DATEDIFF ( employees[hire_date], SELECTEDVALUE ( calendar[Date] ), DAY ) > 90
&& employees[Col_week] <> 7
&& employees[Col_week] <> 6
)
)Col_weekday = WEEKDAY('employees'[hire_date])
If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the answer.
What we need is date difference between employee hire date and selected calendar date but excludes the weekend from the calculation between the 2 dates. Something like below and the calendar table is not connected to fact table.
employee_count_90days = calculate(distinctcount(employees[employee#]), FILTER(employees, employee_status = "Active" && DATEDIFF(employees[hire_date],selectedvalue(calendar[Date]),DAY) - (**weekends between the hire date column and slicer selected date ) > 90))
- v-henryk-mstf
Hi Anonymous ,
It is not yet clear, is it possible to provide test data and screenshots of the expected results?
Best Regards,
Henry