Forum Discussion
Power BI M-Query to Compare Dates
Dear Experts,
Hope you all are doing well!
I am stuck at converting an Excel formula to M-Query. Here is my requirement:
The Excel formulas are as shown below:
Ever on pay roll =COUNTIF('Payroll_Info'!$V:$V,"<"&AB$6)-(COUNTIF('Payroll_Info'!$W:$W,"<"&AA$6)+COUNTIFS('Payroll_Info'!$R:$R,"<"&AA$6,'Payroll_Info'!$Q:$Q,"Suspended - Was previously on pay roll"))
Below are the definition of Columns:
'Payroll_Info'!$V:$V ---> Joining Date of Employee
AB$6 ---> Next Selected Month (e.g. Feb 21 for AA$6 = Jan 21) (6th row of Excel sheet; There are 55k rows)
AA$6 ---> Current Selected month value (e.g. Jan 21)
'Payroll_Info'!$W:$W ---> Separation Date of Employees
'Payroll_Info'!$R:$R ---> Employee Status Date
'Payroll_Info'!$Q:$Q ---> Employee Status
I need to convert this formula to M-Query by adding a custom column "Ever on pay roll" into my model. There are 5 custom columns I need to create like this and then I need to append all those 5 Custom columns into a slicer so that when user click on any of the 5 options they can see the corresponding records for complete month wise and then Quarter wise and then year wise.
Please help!
Thank you
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.
5 Replies
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.
Hi Shankar_BI ,
This error occurs because you did not click on the value in the slicer, the correct value will be displayed after clicking
The measure cannot be put into the slicer, you can put the date field into the slicer, and use the Multi-row card to put the three measures together to display
You said that the report can display monthly values as well as QTD, LTD.... This seems to be inconsistent with your Excel formula above. Can you describe it?
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.
Thank you!!
I am able to fix this issue.
Best Regards
Hi Shankar_BI ,
Has your problem been solved? If it is solved, you can mark the correct answer.
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.
