Forum Discussion
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+_count32. 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.
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+_count32. 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.
Hello,
Thanks for your suggestions!
I tried to implement the same logic but I am getting the following error:
Meanwhile I am not authorized to share the data but as the data you prepared looks good to me so I used your PBIX file and added some more data. (Edit: I did not find any option to attach the PBIX)
There should be three measures i.e. Ever on Payroll, On the Job & Separated. We need to combine these three measures into a single Slicer "Metrics" so that when an user select any of the value from the slicer then the report can display the monthly values along with QTD, LTD etc.
Please find the attached and suggest if anything possible like that using DAX.
