Calculate the distinct count of employees available between two dates

[Anonymous]

Hi, I have these two dummy tables:

The first table contains the ID of employees that are associated to a given index. The second table is a calendar table where those indexes are associated to a given date (sorry for the italian, I couldn't change it - howerever those dummy dates are January 1st to January 3rd 2019).

Those two tables have a "many-to-one" relationship through the Index columns. On the report I use a slicer on the Date column of the calendar table

I need to calculate the distinctcount of Employees at the start date (which I did using the following formula) 

Employees_start = CALCULATE(DISTINCTCOUNT(Employees[Personnel_number]);FILTER(Employees;Employees[Index]=MIN(Employees[Index])))

and the distinctcount of Employees at the end date that were also available at the start date.

Hence, for instance, if the start date is January 1st and the end date is January 3rd, I would expect that the distinctcount of Employees is 3, because only A, C and D were available at both dates.

Can anyone help? Thanks in advance for your support, Power BIers!

[Anonymous · Accepted solution]

@Anonymous - You could try something like this:

1. Calculate a table of the distinct employees that were available at the start.

2.Calculate a table of the distinct employees that were available at the end.

3. Join the 2 tables.

4. Count rows.

Employee Start and End = 
var a = CALCULATETABLE (
    DISTINCT(Employees[Personnel_number]);
    FILTER ( Employees; Employees[Index] = MIN ( Employees[Index] ) )
)
var b =
CALCULATETABLE (
    DISTINCT(Employees[Personnel_number]);
    FILTER ( Employees; Employees[Index] = MAX( Employees[Index] ) )
)
var c = NATURALINNERJOIN(a;b)
return COUNTROWS(c)

Cheers!

Nathan