Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
I am trying to create a measure, I have got as far as the below:
Completion % = var thiscalendardate = MAX('DatesTally'[CalendarDate]) return divide(sum(IF(MAX('Calls'[Completion Date]) > thiscalendardate, 1, 0)),
count(Calls[Job]))
I am trying to calculate the percentage of jobs scheduled per month which were completed in that month
So I have two tables, a Dates table called Datestally, which contains all of the schedule dates. And I have a table called Calls which contains all jobs with their completion dates:
Datestally table:
Date YearMonth
01/01/2018 2018-01
...
30/11/2018 2018-11
Calls table:
Job Sch Date Completion Date
125526 01/05/2018 30/05/2018
135524 02/05/2018 01/06/2018
135598 05/05/2018 29/05/2018
The measure will be used in a line chart, where the YearMonth is on the X axis, and I would want the measure on the Y axis.
For the above data, the measure would show:
2 / 3 = 66.66%
ie 2 jobs which were scheduled for May 2018 were also completed in May 2018
However, the measure I am usng above is returning an error at the moment:
The SUM function only accepts a column reference as an argument
Any ideas where I could be going wrong??
Many thanks for all help
Cheers
Solved! Go to Solution.
Hi @ansa_naz
Please try this measure
Completion % = var thiscalendardate = MAX('DatesTally'[CalendarDate]) return divide(CALCULATE(COUNTROWS(Calls),Filter(Calls,'Calls'[Completion Date] > thiscalendardate)), count(Calls[Job]))
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
hi, @ansa_naz
After my test, SUM needs a column not you could try this formula as below:
Add a year month column for column Sch Date or Completion Date.
then
Completion % = var thiscalendardate = MAX('DatesTally'[Date]) return divide(sumx(FILTER(Calls,Calls[Completion YEAR MONTH]=MAX(DatesTally[YEAR MONTH])),IF(MAX('Calls'[Completion Date]) > thiscalendardate, 1, 0)) ,count(Calls[Job]))
here is pbix, please try it.
Best Regards,
Lin
Hi @ansa_naz
Please try this measure
Completion % = var thiscalendardate = MAX('DatesTally'[CalendarDate]) return divide(CALCULATE(COUNTROWS(Calls),Filter(Calls,'Calls'[Completion Date] > thiscalendardate)), count(Calls[Job]))
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan