Count of Employees

[Saad_Hanif456]

Dear Experts,

I wanted to know how many employees joined in both years from the following sample data.

• If I am using the distinct count function on code then it is calculating the employee twice since the code is different but the employee’s name and CNIC is the same.
• We can only calculate the headcount by CNIC number since it is the same in 2023 and 2023 like Mr. Ali in the sample data, using the distinct count function but if I use that it counts Mr. Ali on Jan 24 as well where it has already been joined on Jan 2023.

Please suggest the DAX calculation as suggested in the required table:

Sample Data:

S.No	Code	Name	CNIC	Joining Date
1	10438	Imtiaz	45555887771	01-01-23
2	10320	Safdar	45555887772	01-01-23
3	10553	Ali	45555887773	01-01-23
4	10042	Hussain	45555887774	01-01-23
5	10399	Arbaas	45555887775	01-02-23
6	10953	Waseem	45555887776	01-02-23
7	10993	khurram	45555887777	01-02-23
8	10164	adil	45555887778	01-02-23
9	10847	sunny	45555887779	01-02-23
10	10959	maheen	45555887780	01-03-23
11	10541	Idrees	45555887781	01-03-23
12	10326	Ijaz	45555887782	01-03-23
13	10507	Sibghat	45555887783	01-03-23
14	10319	Alina	45555887784	01-03-23
1	10333	Ali	45555887773	01-01-24
2	10945	zakir	45555887774	01-01-24
3	10951	shehzad	45555887775	01-01-24
4	10671	Muslim	45555887776	01-01-24
5	10392	ghulam	45555887777	01-02-24
6	10011	rashid	45555887778	01-02-24
7	10796	khurram	45555887779	01-02-24
8	10606	adil	45555887780	01-02-24
9	10328	sunny	45555887781	01-02-24
10	10401	maheen	45555887782	01-03-24
11	10372	Idrees	45555887783	01-03-24
12	10058	Ijaz	45555887784	01-03-24
13	10735	Sibghat	45555887785	01-03-24
14	10507	Zakir	45555887774	01-03-24

Required result:

Year	Month	No. of Emp	Required
2024	Jan	4	3
2024	feb	5	5
2024	Mar	5	4
		14	12

Regards,

Saad Hanif

[Saad_Hanif456]

I have also attached the sample PBIX files in the google drive, need to calculate the Hires month wise and condition mentioned in the remarks column

https://drive.google.com/file/d/1f2A6KLuCjnq05UxS0xB8Li7XD-E9SqgG/view?usp=sharing

Please help me in this regards so that i am able to move forward.

[Saad_Hanif456]

Dear Experts,

Please help me in this regards

Regards,

Saad Hanif

[wdx223_Daniel]

=COUNTROWS(Except(SUMMARIZE(Table1,Table1[CNIC],Table1[Name]),CALCULATETABLE(SUMMARIZE(Table1,Table1[CNIC],Table1[Name]),'Table1'[Joining Date]<MIN('Calendar'[Date]),ALL('Calendar'))))

[Saad_Hanif456]

Thank you for the help but it is not happening, may be i have a slicer of Calendar [month] and calendar [year]