Forum Discussion
Headcount on dashboard is not the same when i'm filtering a month and year.
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
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
Community Champion
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 __ResultHello 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. 🙂 🙂