Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

count rows in a period

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?

2 REPLIES 2
rocky09
Solution Sage
Solution Sage

@Anonymous

Can you post sample data of both tables?

Anonymous
Not applicable

 

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

 

  • EE.JPG

And my Calendar table is as follows:

 

 Calendar.JPG

 

 

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.