Get count of associates depending on the date range

[dasaritr]

Hi All,

I have the following sample dataset

Associate ID	Status	Date load date
A1	Active	12/1/2021
A1	Active	12/2/2021
A1	Terminated	12/3/2021
A2	Active	12/1/2021
A2	Active	12/2/2021
A2	Active	12/3/2021

The requirement is to see the number of active employees till a chosen date. For example, if the user wants to see how many active employees are there at a chosen time period. For example (considering 12/3/2021 is the current date) , the number of Active employees are 1 (since A1 got terminated on 12/3/2021, we have only A1 as an active employee). However if the user chooses 12/2/2021 he/she should see that the number of active employees was 2. 

How do i achieve this in powerbi? Also i need to show the list of active employees as of the chosen date.

Any help on this would be highy appreciated.

Regards

Aritra

[amitchandak]

@dasaritr , With an independent date table used in the slicer and visual

Active employee =
calculate(distinctcount(Table[Associate ID]),filter(employee,employee [Status]="Active" && Table[Date load date])) -
calculate(distinctcount(Table[Associate ID]),filter(employee,employee [Status]="Terminated" && Table[Date load date]))

or

with date table joined to Date load date

Active employee =
calculate(distinctcount(Table[Associate ID]),filter(employee,employee [Status]="Active" && Table[Date load date]),CROSSFILTER(employee[Date load date],date[date],None)) -
calculate(distinctcount(Table[Associate ID]),filter(employee,employee [Status]="Terminated" && Table[Date load date]),CROSSFILTER(employee[Date load date],date[date],None))

[Mahesh0016]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.