Calculating Audit Completions over time from Changing Roster of Employees

Hi  bigchippah ,

Here are the steps you can follow：

1. Create calculated table.

Table = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

2. Create calculated column.

Add a week and count to Audit Results:

week = WEEKNUM('Employee Roster'[Date],1)

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

Add a week to Employee Roster:

week = WEEKNUM('Employee Roster'[Date],1)

3. Create measure.

# of Employees =
var _min=MINX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
var _max=MaxX(ALLSELECTED('Audit Results'),'Audit Results'[Date])
return
COUNTX(FILTER(ALL('Employee Roster'),
'Employee Roster'[week] >=WEEKNUM(_min,1)&&
'Employee Roster'[week] <=WEEKNUM(_max,1)&&
'Employee Roster'[Department]=MAX('Employee Roster'[Department])&&
'Employee Roster'[week]=MAX('Employee Roster'[week])
),[Department])

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])))

4. Result:

Take the [Date] of the Table table as the slicer.

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