I need to create a daily average measure for resolved complaints for each staff member, but am having a difficult time. I have a table with a list of complaints resolved, a calendar table with a working days indicator, and a staff list.
My output should be a one row table that averages the number of complaints closed per working day for today. It will update the average each day as the data is refreshed.
I created a measure that counts the number of closed complaints, [Count of Closed Complaints]. I also created a measure that calculates the elapsed number of working days, [Elapsed Working Days] which is =Day(Today())-TOTALMTD(calculate(count(Calendar[work hours]), Calendar[Work Hours]=0), Calendar[DateKey])
These two measures appear to work correctly.
However, the daily changing average is driving me nuts. I have tried the following formulas (as well as many others that use TOTALMTD) and cannot get it to work:
test average 4 :=CALCULATE(([Count of Closed Complaints]), FILTER(ALLSELECTED(calendar), calendar[Datekey] <= max(calendar[datekey])))/[Elapsed Working Days]
test average 3 :=calculate(([Count of Closed Complaints]/[Elapsed Working Days]), calendar[work hours]>0, DATESMTD(Calendar[DateKey]))
test average 2 :=calculate(AVERAGEA([Count of Closed Complaints]), DATESMTD(Calendar[DateKey]), calendar[work hours]>0)
Please help. Thank you!
I may have misunderstood what you are trying to do but it seems as though you are overcomplicating things.
Your average is simply your [Count of Closed Complaints] divided by [Elapsed Working days]. Consider a measure as follows:
This measure should return the Number of Closed Complaints per working day for all contexts that you want. So if you filter on one staff member for one month you will get the Number of Closed Complaints per day for that staff member for that month.
To make this work you will need to ensure that your Calendar table contains one record per day up to today. That will mean that your average for the current month will always be correct. Your Elapsed Working Days measure can be simplified to