Power BI M-Query to Compare Dates

Hi  Shankar_BI  ,

It would be better to use the Dax function

I created some data:

Table：

Date Table：

1. Create measure.

Ever on payroll =
var _Currentvalue=SELECTEDVALUE('Date'[Current Selected month value])
var _count1=COUNTAX(FILTER(ALL('Table'),'Table'[Joining Date of Employee]<DATE(YEAR(_Currentvalue),MONTH(_Currentvalue)+1,DAY(_Currentvalue))),'Table'[Employee])
var _count2=COUNTAX(FILTER(ALL('Table'),'Table'[Separation Date of Employees]<_Currentvalue),'Table'[Employee])
var _count3=COUNTAX(FILTER(ALL('Table'),'Table'[Employee Status Date]<_Currentvalue&&'Table'[Employee Status]="Suspended - Was previously on pay roll"),'Table'[Employee])
return
_count1-_count2+_count3

2. Use [Current Selected month value] of the Date table as the slicer

3. Result：

If the answer is not what you expected, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

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