Calculating Employee Headcount per month in PowerPivot

Question

I've searched all over for an answer to this but nothing quite seems to match what I need OR it doesn't work for me :(

I'm trying to calculate the number of employees (using an FTE numeric field - not just counting rows) per month.

My data is currently:

Master Worker table: Employee ID (unique), Start Date, Termination Date.

Calendar table.

There is a relationship between Master Worker and Calendar on Hire Date (not sure this is right though).

I have tried:

Workers employed:=VAR currentDate =
    MAX ( 'DimDate'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ('Master Worker'),
        FILTER (
            'Master Worker',
            ('Master Worker'[Start Date USE]<= currentDate
                && 'Master Worker'[Termination Date USE] >= currentDate )
                        )
    ) 

But this returns what looks like the people who started (i.e. with Start Dates) in the relevant month when sliced on the front end.

What I need as an output is a table that I can slice by month (or put on a chart with a month and year axis) that shows the total number of employees employed in that period - not hires or terminations.

I'm totally at a loss here nothing seems to be working! Any help greatly appreciated! :)

editing to add: This output would be great in a separate table if possible if a measure isn't possible:

Date	Total FTE
01/01/2022	2500
01/02/2022	2510
01/03/2022	2505
01/04/2022	2501
01/05/2022	2500
01/06/2022	2530
01/07/2022	2532
01/08/2022	2501
01/09/2022	2501
01/10/2022	2500
01/11/2022	2530
01/12/2022	2510
01/01/2023	2505
01/02/2023	2501

Answer 1

Your relationship is based on hire on date which is the trick here since it might be sufficient to calculate employment status by month indirectly without a direct relationship for this calculation. I would go for manipulating the calendar table and looping over months while filtering based on the conditions. (try to review your relationship)

Try to update your measure like below :

Employees Employed:= 
VAR currentDate = MAX('Calendar'[Date])
RETURN
    CALCULATE(
        SUM('Master Worker'[FTE]), 
        FILTER(
            'Master Worker', 
            'Master Worker'[Start Date] <= currentDate && 
            (ISBLANK('Master Worker'[Termination Date]) || 'Master Worker'[Termination Date] >= currentDate)
        )
    )

---

Active Employees:=
VAR currentDate = MAX('Calendar'[Date])
RETURN
    CALCULATE(
        SUMX(
            'Master Worker',
            IF(
                'Master Worker'[Start Date] <= currentDate &&
                'Master Worker'[Termination Date] >= currentDate,
                1, // Assuming each row represents 1 FTE
                0
            )
        ),
        ALL('Calendar')
    )

Answer 2

The key here is to use ALL to remove the context from the relationship. Additionally, you'll need to cater for those who don't have a Termination date.

Try:

Workers employed:=
  var currentDate  = MAX ( 'DimDate'[Date] )

  return
    CALCULATE(
      COUNTROWS('Master Worker'),
      ALL('DimDate'),
      'Master Worker'[Start Date USE] <= currentDate &&
      (
        'Master Worker'[Termination Date USE] > currentDate ||
        ISBLANK('Master Worker'[Termination Date USE])
      )
    )

If you don't want to count rows, then replace COUNTROWS('Master Worker') with the following:

DISTINCTCOUNTNOBLANK('Master Worker'[Employee ID])

If you have a default termination date set way into the future (ie non-blank) then you can have:

Workers employed:=
  var currentDate  = MAX ( 'DimDate'[Date] )

  return
    CALCULATE(
      COUNTROWS('Master Worker'),
      ALL('DimDate'),
      'Master Worker'[Start Date USE] <= currentDate &&
      'Master Worker'[Termination Date USE] > currentDate
    )

Alternative if DISTINCTCOUNTNOBLANK is not available.

Workers employed:=
  var currentDate  = MAX ( 'DimDate'[Date] )

  return
    CALCULATE(
      COUNTROWS(DISTINCT('Master Worker'[Employee ID])),
      ALL('DimDate'),
      'Master Worker'[Start Date USE] <= currentDate,
      'Master Worker'[Termination Date USE] > currentDate
    )