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 ,

 

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

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

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

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

        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)