CODEWITHSUNDEEP
powerbidax
Roberto
Roberto

Reputation: 9

How to work out total headcount per Month and Year using two date columns?

I have a table called Staff_Starters (https://www.dropbox.com/s/khowqfgytc6fvq8/data.csv?dl=0) which is connected to a Date dimension.

Is it possible to create a calculated column or measure in Staff_Starters which counts everyone that has a DateEmployed & stops counting going forward when it has a Termination_Date.

The formula would count individual multiple time until their end date is not equal to current date.

I currently have a table called Staff_Starterts with the main columns:

  1. StaffID
  2. FullName
  3. DateEmployed
  4. Termination_Date
  5. Team Member
  6. Status
  7. Practice
  8. Region
  9. Cluster
  10. Level

[Starters, Leavers & Nett][1]

The formula used: Headcount Movement = CALCULATE( COUNTROWS(Staff), FILTER(Staff, AND(Staff[Date Employed]<=MIN(Date_Dim[Calendar_Date]), Staff[Termination_Date]>=MAX(Date_Dim[Calendar_Date]))))

Upvotes: 0

Views: 3599

Answers (1)

user5226582
user5226582

Reputation: 1986

Count = CALCULATE(                                   
  COUNTROWS(data),
  FILTER(data, 
      AND(data[Date Employed]<MIN('Calendar'[Date]),
          data[TerminationDate]>MAX('Calendar'[Date])
      )
  )
)

Results in

Date        Count
2017-01-01  85
2017-02-01  74
2017-03-01  58
2017-04-01  37

Edit: In response to a comment, explanation follows. (quotes from msdn)

CALCULATE:

Evaluates an expression in a context that is modified by the specified filters

COUNTROWS: (obvious, but keeping for completeness)

counts the number of rows in the specified table, or in a table defined by an expression

FILTER:

Returns a table that represents a subset of another table or expression.

AND:

Checks whether both arguments are TRUE.

AND(data[Date Employed]<MIN('Calendar'[Date]),

Includes only entries with Date Employed before the start of current selected period.

    data[TerminationDate]>MAX('Calendar'[Date])

Includes only entries with TerminationDate after the end of current selected period.

This should be enough to adapt the measure to count "leavers", "starters" and so on, as long as you can define it in terms of comparing start, termination and selected period dates.

Upvotes: 2

Related Questions