Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • rocky09's avatar
    rocky09
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Anonymous

    Can you post sample data of both tables?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

       

      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?