count of employee who have joined in that particular month

[Anonymous]

Hello Folks,
I am new to PowerBI & Dax, please help me to get this logic for creating New Joiners Measure.

I am trying to calculate Distinct Count of Employee keys (New Employees) for each month based on - DOJ (sample data shown in below table)

Month	Employee key	DOJ
1/1/2022	1111	13/09/2021
1/1/2022	1112	21/01/2022
1/1/2022	1113	28/01/2022
2/1/2022	2222	15/05/2021
2/1/2022	2220	13/02/2022

Output
Month	New Joiners
Jan-22	2
Feb-22	1

Any help or guidance is greatly appreciated.

@ChandeepChhabra @Jihwan_Kim 

Thanks,

Naveen

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure =
CALCULATE (
    COUNT ( 'Table'[Employee key] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Month] ),
        'Table'[DOJ] >= SELECTEDVALUE ( 'Table'[Month] )
    )
)

If I have misunderstood your menaing, please provide your pbix file without privacy information and desired output with more details.

Best Regards
Community Support Team _ Polly

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

[Anonymous]

Hi Amit,

Thanks for the solution, But this is not working on my tables as I cannot use month column from the DATE table as per my other requirements of metrics. 
 Please suggest any other alternative solution by using DAX . 

[amitchandak]

@Anonymous , create a date table with month

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

Create a meausre

distinctcount(Table[Employee key])

Use with month of date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Join with DOJ 

and then create a measure