topic Re: New Joiner Count in DAX Commands and Tips

New Joiner Count

Saad_Hanif456 — Thu, 06 Jun 2024 11:00:01 GMT

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

Re: New Joiner Count

Saad_Hanif456 — Fri, 07 Jun 2024 04:02:13 GMT

Dear Power BI Experts,

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

Regards,

Saad Hanif

Re: New Joiner Count

Anonymous — Fri, 07 Jun 2024 04:24:59 GMT

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.

Re: New Joiner Count

Saad_Hanif456 — Fri, 07 Jun 2024 04:50:44 GMT

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

Re: New Joiner Count

Anonymous — Fri, 07 Jun 2024 05:05:34 GMT

Hi Saad_Hanif456 ,

You can copy your date table.

Table = VALUES('Calendar')

Re: New Joiner Count

Saad_Hanif456 — Fri, 07 Jun 2024 05:24:33 GMT

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.

Re: New Joiner Count

Anonymous — Fri, 07 Jun 2024 05:35:18 GMT

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) )

Re: New Joiner Count

Saad_Hanif456 — Fri, 07 Jun 2024 05:38:56 GMT

Dear Anonymous ,

Perfect brother.

Thank you so much.

Regards,

Saad Hanif

Re: New Joiner Count

Saad_Hanif456 — Fri, 07 Jun 2024 05:45:06 GMT

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

Re: New Joiner Count

Saad_Hanif456 — Fri, 07 Jun 2024 06:36:24 GMT

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

Re: New Joiner Count

Anonymous — Fri, 07 Jun 2024 06:59:50 GMT

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)

Re: New Joiner Count

Saad_Hanif456 — Fri, 07 Jun 2024 07:06:36 GMT

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