New Joiner Count

[Saad_Hanif456]

Dear Experts,

Need to calculate how many employees joined each month but the trick is in the following table example: (Remarks column is given for help)

S.No	Payroll Month	Code	Name	CNIC	Hiring Date	Remarks
1	1-Jan-24	10438	Imtiaz	45555887771	1-Jan-24	Hired
2	1-Jan-24	10320	Safdar	45555887772	1-Feb-23
3	1-Jan-24	10553	Ali	45555887773	1-Oct-23
4	1-Jan-24	10042	Hussain	45555887774	1-Jan-24	HIred
5	1-Feb-24	10438	Imtiaz	45555887771	1-Jan-24
6	1-Feb-24	10320	Safdar	45555887772	1-Feb-23
7	1-Feb-24	10553	Ali	45555887773	1-Oct-23
8	1-Feb-24	10042	Hussain	45555887774	1-Jan-24
9	1-Feb-24	10953	Waseem	45555887776	1-Feb-24	Hired
10	1-Feb-24	10993	khurram	45555887777	1-Feb-24	hired
11	1-Mar-24	10555	Imtiaz	45555887771	1-Mar-24	Confirmed with new Emp ID, CNIC is same
12	1-Mar-24	10556	Hussain	45555887774	1-Mar-24	Confirmed with new Emp ID, CNIC is same
13	1-Mar-24	10399	Arbaas	45555887775	1-Feb-24
14	1-Mar-24	10993	khurram	45555887777	1-Feb-24
15	1-Mar-24	10959	maheen	45555887780	1-Mar-24	Hired
16	1-Apr-24	10555	Imtiaz	45555887771	1-Mar-24
17	1-Apr-24	10556	Hussain	45555887774	1-Mar-24
18	1-Apr-24	10959	maheen	45555887780	1-Mar-24
19	1-Apr-24	10541	Idrees	45555887781	25-Mar-24	He was hired in March 2024 but was included in April 2024 payroll. I want him to be counted in the month of March 2024 only as per hiring date
20	1-Apr-24	10557	Waseem	45555887776	1-Apr-24	Confirmed with new Emp ID
21	1-Apr-24	10558	khurram	45555887777	1-Apr-24	Confirmed with new Emp ID

1- Imtiaz and safdar joined in Jan 2024 and confirmed in Mar 2024 with a new emp ID, CNIC is the same. I do not want to count them in March 2024 because they were already counted in Jan 2024. (That i have already done with DAX ), result as follows:

Months	Result
Jan	2
Feb	2
Mar	1
Apr	0

2 - However, Mr. idrees who was joined in March 2024 is not being counted since he has been added in the Payroll of April 2024

I want his count reflects in the month of March 2024 but the with aforemention DAX he is not refelecting in March 2024 neither in April 2024

Months	Result	Required
Jan	2	2
Feb	2	2
Mar	1	2
Apr	0	0

Please help in this regards and suggest the DAX where my both 1 and 2  condition gets fulfilled so that i can move forward quickly.
PBX FIle is enclosed for working (https://drive.google.com/file/d/1N7qHOwNoO9nsP_bkmLxt4keIBanrcxAA/view?usp=sharing )

Regards
Saad Hanif

[Anonymous · Accepted solution]

Hi @Saad_Hanif456 ,

Try this.

Measure = 
VAR _a = MIN('Calendar'[Date])
var _b = EOMONTH(_a,0)
RETURN 
CALCULATE(
    DISTINCTCOUNT(Payroll[CNIC ]),
    FILTER(ALL(Payroll),[Firsthiringdate] >= _a && 'Payroll'[Firsthiringdate] <= _b)
)

[Anonymous]

Hi @Saad_Hanif456 ,

Regarding your question, I checked the data. Did you miss 'Arbaas'? He should be counted as a new employee in February, right?

Please follow these steps:

1.Delete the relationship between tables. It affects filtering.

2.Use the following DAX expression to create a measure

Measure = 
VAR _a = MIN('Calendar'[Date])
var _b = EOMONTH(_a,0)
RETURN 
CALCULATE(
    DISTINCTCOUNT(Payroll[CNIC ]),
    FILTER(
        Payroll,
        Payroll[firsthiringdate] >= _a && 
        Payroll[firsthiringdate] <= _b))

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Saad_Hanif456]

Dear @Anonymous ,

Thank you so much for the help but i need the relationship in Calendar [Date] with Payroll [Payroll Month] active so that my other DAX would not get impacted..

Yes, you are right, I missed Mr. Arbass, he should be reflected in month of Feb 2024

Do you have any other idea with active relationship?

Regards,

Saad Hanif

[Anonymous]

Hi @Saad_Hanif456 ,

You can copy your date table.

Table = VALUES('Calendar')

[Saad_Hanif456]

Dear @Anonymous ,

It is not working in my sheet , i tired the same.

It will not be benefical for me because I have a slicer of Calendar [Month] and Calendar [Year], if I use the new calendar table Table[date] for the sake of DAX Works that Calendar table slicer will not work accordingly.
Therefore, I need the relation active with the same Payroll [Payroll Month] and Calender [Date] and uses DAX so that the silcer could also work accordingly.

[Anonymous · Accepted solution]

Hi @Saad_Hanif456 ,

Try this.

Measure = 
VAR _a = MIN('Calendar'[Date])
var _b = EOMONTH(_a,0)
RETURN 
CALCULATE(
    DISTINCTCOUNT(Payroll[CNIC ]),
    FILTER(ALL(Payroll),[Firsthiringdate] >= _a && 'Payroll'[Firsthiringdate] <= _b)
)

[Saad_Hanif456]

Dear @Anonymous,
What if i want to look in the following way in the table visual:

Column Names:
Month - Names - Count

Then what should be added in the same DAX.

Regards

Saad Hanif

[Saad_Hanif456]

Dear @Anonymous ,

Please suggest this as well?

What if i want to look in the following way in the table visual:

Column Names:
Month - Names - Count

Then what should be added in the same DAX.

Regards

Saad Hanif

[Anonymous]

@Saad_Hanif456 ,

Measure = 
VAR _a = MIN('Calendar'[Date])
var _b = EOMONTH(_a,0)
VAR _c = CALCULATE(
    DISTINCTCOUNT(Payroll[CNIC ]),
    FILTER(
        ALL(Payroll),
        Payroll[firsthiringdate] >= _a && 
        Payroll[firsthiringdate] <= _b))
VAR _d = CALCULATE(CONCATENATEX(SUMMARIZE('Payroll',[Name ]),[Name ],","),
    FILTER(
        ALL('Payroll'),
        Payroll[firsthiringdate] >= _a && 
        Payroll[firsthiringdate] <= _b))


RETURN IF(ISBLANK(_c),BLANK(),
"Month:" & MONTH(_a) & "-" & _d  & "-" & _c)

[Saad_Hanif456]

Dear @Anonymous ,

Brother, I just want the following Column Names in the Table Visual:
For example:
Month | Name | Count

Not a need of concatenate it.

Actually, when i am adding the Payroll[Name] Column in the table Visual alongwith Payroll[Month] and [New HIre DAX] that DAX does not show that New Hires Name. 
Regards,
Saad Hanif

[Saad_Hanif456 · Accepted solution]

Dear @Anonymous ,

Perfect brother.

Thank you so much.

Regards,

Saad Hanif

[Saad_Hanif456]

Dear Power BI Experts,

Please help me in this regards, I am stuck in this scenario.

Regards,

Saad Hanif