Forum Discussion

FlorisMK's avatar
FlorisMK
Helper I
1 year ago
Solved

Workforce development graph based on contract dates

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 purpos...
Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

DataNinja777's avatar
DataNinja777
Super User
1 year ago

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,