Forum Discussion

bigchippah's avatar
bigchippah
Helper I
4 years ago

Calculating Audit Completions over time from Changing Roster of Employees

Hello, I am trying to calculate the number/percentage of audits of a team as they are audited over a time period.  I have been struggling with an efficient way to accomplish this task and I'm no...
Need Help
bigchippah's avatar
bigchippah
Helper I
4 years ago

AnonymousHello and thank you for the answer!  I'm trying to work my way through the steps and I've encountered a couple of error messages:
In the "Add a week and count to Audit Results" step, the following code:

count = COUNTX(FILTER(ALL('Audit Results'),'Audit Results'[week]=EARLIER('Audit Results'[week])&&'Audit Results'[Department]=EARLIER('Audit Results'[Department])),[Department])

returns an error that says, "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

Secondly, when I use this code:

Audited =
var _min=MINX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
var _max=MaxX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
return
CALCULATE(SUM('Audit Results'[Column]),FILTER(ALL('Audit Results'),MAX('Audit Results'[week])>=WEEKNUM(_min,1)&&MAX('Audit Results'[week])<=WEEKNUM(_max,1)&&'Audit Results'[Department]=MAX('Employee Roster'[Department])))

can you clarify which column 'Audit Results'[Column] should use? 

Thank you for your time and assistance!  I am very greatful!