How to dynamically filter rows from a table by a non related date slicer?

[Anonymous]

I have a table of Employee ids and the day they were hired and the day they left the company. people with null termination date are still working in the company. New employee ids are appended to this table at the end of each month.

I have another table which contains the departments they have worked so far and the day they started work in that department. Here employee ids are duplicable since one's department may have changed within his tenure. 

There is a date slicer in my report and using below query im able to count the employees who worked in the company for a selected period.

FTE = var withTdate = COUNTROWS(FILTER(FTE,FTE[Hire Date]<=MIN('Date'[Date]) && FTE[Leavers.Termination Date]>=MAX('Date'[Date])))

var stillworking = COUNTROWS(FILTER(FTE,FTE[Hire Date]<=MIN('Date'[Date]) && FTE[Leavers.Termination Date]=BLANK()))

return withTdate + stillworking

Now I need a way to get the department wise employee count. Since one employer might have worked in several departments within a selected period im only going to consider the department they belong on the max date of the slicer. (i couldn't find a date slicer to select a single date.)

these department-wise counts are wrong since it doesn't detect one's changes in the department.

How can I display the correct department wise headcount for the selected maximum day in the slicer?

Thank you in advance for any support...

[v-xuding-msft]

Hi @Anonymous ,

By my test,  you need  to  use DISTINCTCOUNT() function. The following  formula you can have a try.

I have two tables, “Function ”and “Table”. There isn’t a relationship between them.

Employee Count =
CALCULATE (
DISTINCTCOUNT ( Function[Employee ID] ),
FILTER (
Function,
Function[Hire Date] <= MIN ( 'Table'[Date].[Date] )
&& Function[Leavers.Termination Date] >= MAX ( 'Table'[Date].[Date] )
)
)

If you have any other questions, please post your expected result and your table “Date” which will make  us to help you quickly.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I re-edited my original question. hope now you can understand my requirement better.