Forum Discussion

laurent_rio's avatar
laurent_rio
Helper I
5 years ago

Measure by Filter Month

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 A...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.