Headcount on dashboard is not the same when i'm filtering a month and year.

[Zaidan30]

Hello everyone, 

I'm working on excel power query and i have measures of headcount, hires, terminations etc. I created a dashboard of all these KPIs but when i filter the dates that is, if i select 2024 and month April or May the headcount and all other KPIs are becoming as Headcount - 46, Hires - 2, Terminated - 7 etc. Where as the total number by the end or the month must be 3702 in April and 3828 in May. Here are the screen shots. 

This is before any filters

This is after i select the filters ie, May 2024

Please let me know where i'm going wrong? I have created the date_table[Date] and connected it to Hire date and termination date in my data sheet. 

[Zaidan30]

Hello @Greg_Deckler 

Thank you so much for your reply. Let me share all the points with you. Here's the data that i'm working on, 

EMP ID	Name	Start Date	Termination Date
1	Matthieu	1/1/2024
2	Paul	3/27/2024	4/30/2024
3	Rémi	3/27/2024	4/30/2024
4	Guillaume	1/1/2024
5	Isabelle	1/1/2024
6	Agnès	1/1/2024
7	Amelie	9/9/2024
8	Anne Sophie	1/1/2024
9	Olivier	1/1/2024
10	Brice	4/22/2024	12/31/2024
11	Antoine	1/1/2024
12	Dorine	1/1/2024
13	Ophelie	1/1/2024

My expected output is, 

Expected output:
Headcount	10
Terminated	3

Problem I'm having in the dashboard: 

* When I'm using a filter to have the total HC until this particular month in this case September 2024 for eg: If i click September 2024 slicer I'm getting information of September as just 1 Where as the total Headcount until the end of Sep 2024 is 11.

*Here are the screen shots of the measures i have created.

Here's the screenshot of the connection beween HR data and Date table. i have connected Hire date and termination date with Date_Table [Date]

I hope this helps. Thank you so mich in advance for your help. 

[Zaidan30]

Hello @Greg_Deckler ,

Thank you so much for your message. My apologies for not being clear. Here's all he information you asked for, 

Below is the data that i'm working on which had 5000 rows. 

SSO with Zero	Name	Hire Date	Termination Date
1	Matthieu	1/1/2024
2	Paul	3/27/2024	4/30/2024
3	Rémi	3/27/2024	4/30/2024
4	Guillaume	1/1/2024
5	Isabelle	1/1/2024
6	Agnès	1/1/2024
7	Amelie	9/9/2024
8	Anne Sophie	1/1/2024
9	Olivier	1/1/2024
10	Brice	4/22/2024	12/31/2024
11	Antoine	1/1/2024
12	Dorine	1/1/2024
13	Ophelie	1/1/2024

I have an overall expected ouput when i dont use any filters. 

Expected output:
Headcount	10
Terminated	3

Problem I'm having in the dashboard:

* When I'm using a filter to have the total HC until this particular month in this case September 2024 for eg: If i click September 2024 slicer I'm getting information of September as just 1 Where as the total Headcount until the end of Sep 2024 is 11.

*Here are the screen shots of the measures i have created.

My date table is connected to Hires date and termination date with "Date": 

I hope this helps to understand better. 🙂 

[Greg_Deckler · Accepted solution]

@Zaidan30 Perfect! Thanks for the detailed information. I did it like the following. PBIX is attached below signature.

Measure = 
    VAR __MaxDate = MAX( 'Dates'[Date] )
    VAR __Table = FILTER( ALL( 'Table' ), [Hire Date] <= __MaxDate )
    VAR __Table2 = FILTER( ALL( 'Table' ), [Termination Date] <= __MaxDate && [Termination Date] <> BLANK() )
    VAR __Result = COUNTROWS( __Table ) - COUNTROWS( __Table2 )
RETURN
    __Result

[Zaidan30]

Hello @Greg_Deckler ,

I'm so happy you solved my problem. You're really the best. i was working to solve this problem from almost 3 days before posting my request here. Thank you sooo sooo much for your help. 🙂 🙂 

[Greg_Deckler]

@Zaidan30 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.