New hires last 12 months

[thod]

Hi,

I wan't to calculate the distinct number of new hires for the rolling last 12 months, based on a year-month filter the user selects in a slicer.
I have a regular date table, and a subset of the fact table looks like this:

EmpID	StartDate	Value
123	01-06-2022	5
123	01-06-2022	4
333	01-01-2022	7
333	01-01-2022	8
111	01-01-2023	2
111	01-01-2023	2
111	01-01-2023	2

So if the user selects 2023-May in the slicer from the date table, the number of new hires should be 2 (EmpID 111 and 123). If the user selects 2023-Aug the number of new hires should be 1 (EmpID 111).

Any sugestions?
Br.,
Thomas

[rubayatyasmin]

Hey, @thod 

what date format are you using? dd/mm/yyyy ? 

I tried to reproduce your problem. 

Result:

My date format is mm/dd/yy

this thread should help

Solved: Rolling 12 Month Headcount - Microsoft Fabric Community

[thod]

My calendar table is standard and includes columns such as date (dd-mm-yyyy), year, month, Year-Month.
The start date in my fact table is date (dd-mm-yyyy).
Your measure should not include EmpID 123, when you have selected 2022-Jan, because EmpID 123's start date is 1st June 2022.
Br.,
Thomas

[rubayatyasmin]

It included because I mentioned my date format is different than yours. I just gave you an idea. 

Thanks