cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Calculate the distinct count of employees available between two dates

Hi, I have these two dummy tables:employees.PNG

dates.PNG

 

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 tableCapture.PNG

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!

1 ACCEPTED SOLUTION

Accepted Solutions
natelpeterson New Contributor
New Contributor

Re: Calculate the distinct count of employees available between two dates

@walter_mangini - 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

1 REPLY 1
natelpeterson New Contributor
New Contributor

Re: Calculate the distinct count of employees available between two dates

@walter_mangini - 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