Measure by Filter Month

laurent_rio ,  in case these are dates refer to my blog on the same topic. Or create date from month

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Try : https://www.youtube.com/watch?v=e6Y-l_JtCq4

Hi, laurent_rio 

I am not sure about how your data model looks like, but I think it is better to have Dim-Calendar Table.

Please check the below picture and the sample pbix file's link down below whether it is what you are looking for.

All measures are in the sample pbix file.

https://www.dropbox.com/s/bcehj44udr60kmx/laurent.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Hi laurent_rio ,

Please try the following steps:

1. Add month number columns for Hire and Terminate for comparison:

HireMonthNo = MONTH(IFERROR(CONVERT([Hire Month] & " 1" ,DATETIME),BLANK()))

TerminateMonthNo = MONTH(IFERROR( CONVERT([Terminate Month] &" 1",DATETIME),BLANK()))

2. Create a separated table for slicer:

Month for slicer = DISTINCT(SELECTCOLUMNS( CALENDAR("2021/1/1","2021/12/31"),"Month",FORMAT([Date],"MMMM"),"MonthNo",MONTH([Date])))

3. Create a measure and apply it to filter ,set as "=1" to display which meet the condition:

Flag = 
var _active=MAX('Table'[Status])="Active" && MAX('Table'[HireMonthNo])<=MAX('Month for slicer'[MonthNo])  
var _terminate=MAX('Table'[Status])="Terminate" && MAX('Table'[TerminateMonthNo])>=MAX('Month for slicer'[MonthNo])
return IF(_active || _terminate,1,0)

4. Last, count numbers:

Count active employee = SUMX('Table',[Flag])

According to my understanding, when you select February in slicer A,B,C,D,E,F,H are on-the-job, but you said only A,C,D,E,F .

Why don't B and H meet the conditions? Could you please explain to me in more detail? Thanks!

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

Hi laurent_rio ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Hope to hear from you😀

Best Regards,
Eyelyn Qin