Forum Discussion

Saad_Hanif456's avatar
Saad_Hanif456
Icon for Helper I rankHelper I
2 years ago
Solved

New Joiner Count

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  ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Saad_Hanif456
    2 years ago

    Dear Anonymous ,

    Perfect brother.

     

    Thank you so much.

     

    Regards,

    Saad Hanif

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Saad_Hanif456
Icon for Helper I rankHelper I
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Saad_Hanif456 ,

    You can copy your date table.

    Table = VALUES('Calendar')

     

     

    • Saad_Hanif456's avatar
      Saad_Hanif456
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

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