Forum Discussion

Anonymous

Not applicable

4 years ago

Count New Hire Per Month

Hi, I have a table of Headcount that has below fileds and I need to count the new hired per month: Table = Headcount Fields = Report Effective Date (which is the monthly report date, that change...

Frequent Visitor

3 years ago

Hi,

I recently came across the same issue. Here is the solution I used in my report:

New Hires by Period = CALCULATE(COUNTROWS(<Data source>),

FILTER(VALUES([Hire Date]), [Hire Date] <= MAX(Dates[Date])),

FILTER(VALUES([Hire Date]), [Hire Date] >=MIN(Dates[Date])))

Let me know if it worked.

zinah
Regular Visitor
3 years ago
Hi,
Thanks for your suggestion.
I was trying to build the measure that you suggested but this part of the measure is not clear, what do you mean by MAX(Dates[Date])?
As I mentioned in my previous post, these are the fields I have:

Table = Headcount
Fields =
Report Effective Date (which is the monthly report date, that changes every month)
Employee ID
Hire Date
- SpaceDad
  Frequent Visitor
  3 years ago
  Hi zinah,
  
  The "Dates[Date]" is a reference to a table consisting of all the possible dates in the dataset. In order for the solution to work you will need to create a separate table from your data source. The first date in the "Dates" table should be the oldest date in your original data source. For example, the oldest hire date. Then you will want to use the “TODAY()” function, so that the current date is added to the date table as time progresses.
  Once you have a “Dates” table created, you will want to create a relationship between “Hire Dates” and the dates in the “Date” table. After you have done all that, then the previously mentioned formula will work. You should be able to view head count for any given date using which ever visualization works best for your report.
- SpaceDad
  Frequent Visitor
  3 years ago
  Would you like some help with creating a 'dates' table?
  - zinah
    Regular Visitor
    3 years ago
    That would be great.