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

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

       

       

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

        Dear Anonymous ,

        Perfect brother.

         

        Thank you so much.

         

        Regards,

        Saad Hanif