Workforce development graph based on contract dates

[FlorisMK]

I have:

• A People table with all employees' contract start/end dates.
• A Calendar table with all (relevant) dates.
• A slicer to select a year from the Calendar table (already in use for other purposes).

I want:

• A stepped chart of workforce development over time, for the year selected in the slicer (or for the entire periode if no year is selected, but that's trivial). What I want the chart to show:
  • Chart starts with workforce size at the start of the selected year (or entire period)
  • Chart is horizontal until the first contract date, and steps there (1 up for contract start dates, 1 down for end dates)
  • Chart ends with workforce size at the end of the selected year (or entire period)
  • (Also need to stack the chart per division, division per person is in the table, so also fairly trivial)

So far, I've done the heavy lifting for this chart in Excel, and imported the Excel result into Power BI. This is cumbersome, especially since Excel doesn't do stepped charts at all, so I had to do some previous date tricks to get it working at all.

I suspect there's an easier way to do it in Power BI, but I'm not sure how. Use the Calendar dates as input for a measure in the People table, which compares to the contract dates to determine whether the record should be counted? Chart that measure somehow? I'm still getting my head around measures in general, so any help is greatly appreciated!

[FlorisMK]

@DataNinja777 That's great, thanks! The article you link to has a wealth of information on wha I need.

[DataNinja777 · Accepted solution]

Hi @FlorisMK ,

In order to deal with the headcount issue using dax, the best practice is to use the following tables which you have specified in your message:

• The employee table with start and end dates
• Calendar table 

and then set these tables as disconnected tables and write a measure like below:

Headcount =
SUMX (
    EmployeeFact,
    IF (
        EmployeeFact[Start] <= [SelectedDate]
            && EmployeeFact[End] >= [SelectedDate],
        1,
        BLANK ()
    )
)

For the details of how to set up the data model, please refer to the article below:
Dynamic Headcount Analysis using Dax - Microsoft Fabric Community

Best regards,