Measure by Filter Month

[laurent_rio]

Hi,

Can anyone help

What is the formula of measure if i want to check the active employee based on month ( month will be my filter )

Employee	Status	Hire Month	Terminate Month
A	Active	January
B	Terminate	January	March
C	Active	February
D	Active	February
E	Terminate	February	March
F	Terminate	February	March
G	Active	March
H	Active
I

How is the measure to count active employee on specific month (For example if my filter month is February, it will show that active employee = A,C,D,E,F)

Thanks 

[Anonymous]

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

[Anonymous]

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.

[Jihwan_Kim]

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/

[amitchandak]

@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-tr...

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