I would like to count the active headcount for each month but I seem to be running into a road block with the following formula. The fact table (sample below) does not have an active relationship with the calendar table. It is not counting the rows for each month, per Platform, and instead is counting the total # of rows based on the first set of filter criteria in the CALCULATE function but I do not understand why.Active Count = VAR LastDateInPeriod = MAX('Calendar Table'[Date]) VAR FirstDateInPeriod = MIN('Calendar Table'[Date]) RETURN CALCULATE( COUNTROWS('Worker Table'), 'Worker Table'[Worker] = "CTE", 'Worker Table'[Record Type] IN { "Keep Active", "Release - Starting Count", "Conv RBG - CTE Starting Count" }, FILTER( 'Worker Table', 'Worker Table'[Worker Start Date] <= LastDateInPeriod && OR('Worker Table'[Worker End Date] >= FirstDateInPeriod,ISBLANK('Worker Table'[Worker End Date])) ) ) Sample table data (not representative of the full table, but so you know the structure):PlatformIDWorker Start DateWorker End DateWorkerRecord TypePlatform1ID112/01/202504/01/2026CTERelease - Starting CountPlatform1ID211/12/202504/10/2026CTERelease - Starting CountPlatform1ID302/24/202604/17/2026CTERelease - Starting CountPlatform1ID410/22/202504/30/2026CTERelease - Starting CountPlatform1ID510/15/202504/30/2026CTERelease - Starting CountPlatform1ID612/11/202504/30/2026CTERelease - Starting CountPlatform1ID702/07/202405/31/2026CTERelease - Starting CountPlatform1ID6708/04/202508/01/2026CTEConv RBG - CTE Starting CountPlatform1ID14409/01/202510/01/2026CTEConv RBG - CTE Starting CountPlatform1ID27203/27/2023 CTEKeep ActivePlatform1ID27305/05/2022 CTEKeep ActivePlatform1ID27410/23/2024 CTEKeep ActivePlatform1ID27503/27/2023 CTEKeep ActivePlatform1ID27604/15/2025 CTEKeep ActivePlatform1ID27705/29/2025 CTEKeep ActivePlatform1ID27810/29/2024 CTEKeep ActivePlatform2ID27903/08/2023 CTEKeep ActivePlatform2ID28010/08/202504/30/2026CTERelease - Starting CountPlatform2ID28101/28/2026 CTEKeep ActivePlatform2ID28210/20/202512/01/2026CTEConv RBG - CTE Starting CountPlatform2ID28311/10/202506/30/2026CTERelease - Starting CountPlatform2ID28411/03/2025 CTEKeep ActivePlatform2ID28507/21/202506/30/2026CTERelease - Starting CountPlatform2ID28612/01/202512/01/2026CTEConv RBG - CTE Starting CountPlatform2ID28711/10/2025 CTEKeep Active When I select a specific Platform it gives me a smaller number (total rows) but isn't filtering down to the Platform specifically for each month:

Hi chillpill,Thank you for posting your query in the Microsoft Fabric Community Forum. I reproduced your scenario in Power BI Desktop using the sample data provided in the thread and was able to achieve the expected active headcount results by month and by Platform. For my testing, I used a disconnected Calendar table and the following measure: Active Count = VAR LastDateInPeriod = MAX ( 'Calendar Table'[Date] ) VAR FirstDateInPeriod = MIN ( 'Calendar Table'[Date] ) RETURN CALCULATE ( COUNTROWS ( 'Worker Table' ), 'Worker Table'[Worker] = "CTE", 'Worker Table'[Record Type] IN { "Keep Active", "Release - Starting Count", "Conv RBG - CTE Starting Count" }, FILTER ( 'Worker Table', 'Worker Table'[Worker Start Date] <= LastDateInPeriod && ( ISBLANK ( 'Worker Table'[Worker End Date] ) || 'Worker Table'[Worker End Date] >= FirstDateInPeriod ) ) ) The resulting headcount varied correctly across months and platforms based on the worker start/end dates, which aligns with the requirement of counting employees who were active during the selected month. For reference, I have attached the PBIX file that I used to reproduce the scenario. Could you please compare it with your model and confirm: Whether the Month field in your matrix comes from the Calendar table. Whether your Calendar table contains one row per day. Whether there are any active/inactive relationships between the Calendar table and Worker table. Whether the values shown in your visual are coming from this measure. This information will help identify any model-specific differences that may be affecting the results in your environment. Best regards,Ganesh Singamshetty

chillpill

Helper III

2 months ago

Solved

Counting Active employees per month

I would like to count the active headcount for each month but I seem to be running into a road block with the following formula. The fact table (sample below) does not have an active relationship with the calendar table. It is not counting the rows for each month, per Platform, and instead is counting the total # of rows based on the first set of filter criteria in the CALCULATE function but I do not understand why.

Active Count = 
VAR LastDateInPeriod = MAX('Calendar Table'[Date])
VAR FirstDateInPeriod = MIN('Calendar Table'[Date])
RETURN
CALCULATE(
    COUNTROWS('Worker Table'),
    'Worker Table'[Worker] = "CTE",
    'Worker Table'[Record Type] IN {
        "Keep Active",
        "Release - Starting Count",
        "Conv RBG - CTE Starting Count"
        },
    FILTER(
        'Worker Table',
        'Worker Table'[Worker Start Date] <= LastDateInPeriod 
            && OR('Worker Table'[Worker End Date] >= FirstDateInPeriod,ISBLANK('Worker Table'[Worker End Date]))
    )
)

Sample table data (not representative of the full table, but so you know the structure):

Platform	ID	Worker Start Date	Worker End Date	Worker	Record Type
Platform1	ID1	12/01/2025	04/01/2026	CTE	Release - Starting Count
Platform1	ID2	11/12/2025	04/10/2026	CTE	Release - Starting Count
Platform1	ID3	02/24/2026	04/17/2026	CTE	Release - Starting Count
Platform1	ID4	10/22/2025	04/30/2026	CTE	Release - Starting Count
Platform1	ID5	10/15/2025	04/30/2026	CTE	Release - Starting Count
Platform1	ID6	12/11/2025	04/30/2026	CTE	Release - Starting Count
Platform1	ID7	02/07/2024	05/31/2026	CTE	Release - Starting Count
Platform1	ID67	08/04/2025	08/01/2026	CTE	Conv RBG - CTE Starting Count
Platform1	ID144	09/01/2025	10/01/2026	CTE	Conv RBG - CTE Starting Count
Platform1	ID272	03/27/2023		CTE	Keep Active
Platform1	ID273	05/05/2022		CTE	Keep Active
Platform1	ID274	10/23/2024		CTE	Keep Active
Platform1	ID275	03/27/2023		CTE	Keep Active
Platform1	ID276	04/15/2025		CTE	Keep Active
Platform1	ID277	05/29/2025		CTE	Keep Active
Platform1	ID278	10/29/2024		CTE	Keep Active
Platform2	ID279	03/08/2023		CTE	Keep Active
Platform2	ID280	10/08/2025	04/30/2026	CTE	Release - Starting Count
Platform2	ID281	01/28/2026		CTE	Keep Active
Platform2	ID282	10/20/2025	12/01/2026	CTE	Conv RBG - CTE Starting Count
Platform2	ID283	11/10/2025	06/30/2026	CTE	Release - Starting Count
Platform2	ID284	11/03/2025		CTE	Keep Active
Platform2	ID285	07/21/2025	06/30/2026	CTE	Release - Starting Count
Platform2	ID286	12/01/2025	12/01/2026	CTE	Conv RBG - CTE Starting Count
Platform2	ID287	11/10/2025		CTE	Keep Active

When I select a specific Platform it gives me a smaller number (total rows) but isn't filtering down to the Platform specifically for each month:

Need Help

v-ssriganesh
2 months ago
Hi chillpill,
Thank you for posting your query in the Microsoft Fabric Community Forum.

I reproduced your scenario in Power BI Desktop using the sample data provided in the thread and was able to achieve the expected active headcount results by month and by Platform.

For my testing, I used a disconnected Calendar table and the following measure:

Active Count = VAR LastDateInPeriod = MAX ( 'Calendar Table'[Date] ) VAR FirstDateInPeriod = MIN ( 'Calendar Table'[Date] ) RETURN CALCULATE ( COUNTROWS ( 'Worker Table' ), 'Worker Table'[Worker] = "CTE", 'Worker Table'[Record Type] IN { "Keep Active", "Release - Starting Count", "Conv RBG - CTE Starting Count" }, FILTER ( 'Worker Table', 'Worker Table'[Worker Start Date] <= LastDateInPeriod && ( ISBLANK ( 'Worker Table'[Worker End Date] ) || 'Worker Table'[Worker End Date] >= FirstDateInPeriod ) ) )

The resulting headcount varied correctly across months and platforms based on the worker start/end dates, which aligns with the requirement of counting employees who were active during the selected month.

For reference, I have attached the PBIX file that I used to reproduce the scenario.

Could you please compare it with your model and confirm:

Whether the Month field in your matrix comes from the Calendar table.

Whether your Calendar table contains one row per day.

Whether there are any active/inactive relationships between the Calendar table and Worker table.

Whether the values shown in your visual are coming from this measure.

This information will help identify any model-specific differences that may be affecting the results in your environment.

Best regards,
Ganesh Singamshetty
5223399.pbix89 KB

5 Replies

krishnakanth240
Super User
2 months ago
Hi chillpill
Ensure the relationship between Calendar Table (Dim) and Worker Table (Fact) based on 'Date' column to slice from the Month (from Calendar Table) Slicer
Active Headcount =
VAR LastDateInPeriod = MAX ('Calendar Table'[Date])
VAR WorkerFilter =
'Worker Table'[Worker] = "CTE" &&
'Worker Table'[Record Type] IN {"Keep Active", "Release - Starting Count", "Conv RBG - CTE Starting Count"}
VAR ActiveCondition =
'Worker Table'[Worker Start Date] <= LastDateInPeriod &&
(ISBLANK('Worker Table'[Worker End Date]) ||
'Worker Table'[Worker End Date] >= LastDateInPeriod)
RETURN
CALCULATE (COUNTROWS('Worker Table'),
KEEPFILTERS (WorkerFilter),
FILTER (ALL('Worker Table'), ActiveCondition))
- chillpill
  Helper III
  2 months ago
  Hi there, thanks for the help. There is no 'Date' column in the fact table since this is based on worker start and end date, unless you are recommending they be connected to either start date or end date columns?
  - krishnakanth240
    Super User
    2 months ago
    Hi chillpill
    It depends on the requirement to pick either of the date columns
Fenil_Italiya
Advocate I
2 months ago
Hello, chillpill

Could you please provide the expected output of the sample data you have provided.
because based on your current DAX there is no relationship with the datetable so it not provide context based output.
-> Firstly you must have to create a relationship between one of the column of date and then based on that you can get the output.

-> and in which way you want to handle the blank value is also mattered.

-> If you could provide the expected output from the sample data then may be we can provide the soulution you want.

Thank you.
v-ssriganesh
Community Support
2 months ago
Hi chillpill,
Thank you for posting your query in the Microsoft Fabric Community Forum.

I reproduced your scenario in Power BI Desktop using the sample data provided in the thread and was able to achieve the expected active headcount results by month and by Platform.

For my testing, I used a disconnected Calendar table and the following measure:

Active Count = VAR LastDateInPeriod = MAX ( 'Calendar Table'[Date] ) VAR FirstDateInPeriod = MIN ( 'Calendar Table'[Date] ) RETURN CALCULATE ( COUNTROWS ( 'Worker Table' ), 'Worker Table'[Worker] = "CTE", 'Worker Table'[Record Type] IN { "Keep Active", "Release - Starting Count", "Conv RBG - CTE Starting Count" }, FILTER ( 'Worker Table', 'Worker Table'[Worker Start Date] <= LastDateInPeriod && ( ISBLANK ( 'Worker Table'[Worker End Date] ) || 'Worker Table'[Worker End Date] >= FirstDateInPeriod ) ) )

The resulting headcount varied correctly across months and platforms based on the worker start/end dates, which aligns with the requirement of counting employees who were active during the selected month.

For reference, I have attached the PBIX file that I used to reproduce the scenario.

Could you please compare it with your model and confirm:

Whether the Month field in your matrix comes from the Calendar table.

Whether your Calendar table contains one row per day.

Whether there are any active/inactive relationships between the Calendar table and Worker table.

Whether the values shown in your visual are coming from this measure.

This information will help identify any model-specific differences that may be affecting the results in your environment.

Best regards,
Ganesh Singamshetty
5223399.pbix89 KB

Forum Discussion

Counting Active employees per month

5 Replies

Related content

Count active employers

active employee count per month

Active Employees per Period

Count of active employees by year

Count active employees with missing timesheets