count rows in a period

[Anonymous]

Sorry i am a beginner in PowerBI without formal traning.

I established 2 tables:

1) Employee table (every employee is a uniqle row) - they Date Join and Last Working Date (either with a Date or just blank for active employee).

2) Date Table (every single day with category in terms of Years, Quarter, etc,)

I would like to set up 3 measures for counting

1) active headcount as of 1st day of each month,

2) active headcount as of the last day of each month, and

3) leavers in each month

How can i set up these 3 measures?

[rocky09]

@Anonymous

Can you post sample data of both tables?

[Anonymous]

Thanks so much Rocky! My Employee data is like below:

• Every employee only have 1 record
• Last Working Date is either blank or is their Last Working Date

• 

And my Calendar table is as follows:

 

My question is:

1. I need to provide the headcount as of 31 Jan 2018. And the headcount criteria should be:
1. Date Join <= 31 Jan 2018 AND
2. Last Working Date >31 Jan 2018 OR Last Working Date is blank
2. I need to provide the no. of leavers in Jan 2018. And the criteria should be:
   1. Last Working Date >= 1 Jan 2018 AND
   2. Last Working Date <= 31 Jan 2018

I have searched for many sites and try to use the below formular but the result is not correct:

Active Employees =
CALCULATE(
 COUNTA(Employee[p_empno]), FILTER(Employee, (Employee[Date_Join] <= LASTDATE('Calendar'[DateKey]) && (Employee[Last_Working_Date]>= FIRSTDATE('Calendar'[DateKey]) || ISBLANK(Employee[Last_Working_Date])))))

This report will be provided each month with an updated database. Thus the Date will be changed from 31 Jan 2018 to 28 Feb 2018 next time. How could I manage the dashboard with changing the dates and then the data will be reflected correctly?