Index match issue

[KDavidP1987]

Greetings all,

I'm running into an issue with part of an INDEX MATCH array and don't know how to resolve it.

Context: Manager asked that I setup a table to calculate the average calls offered to agents on a 30 day rotation against the days they are WFH (work from home). So, I need needed to get the days they were WFH into a table, and the daily totals of calls they take. Then I needed to merge the data (done), and setup a calculation to provide the 30 day average for each day listed as WFH

Here is the code I came up with to calculate this. (it runs, but the numbers don't seem right)

   =IF([@IsWFHDay]="WFH", AVERAGE(INDEX(Table1[[Agent]:[Calls Answered]],MATCH(1,([Agent]=[@Agent])*([Date]<=[@Date])*([Date]>=[@Date]-30),0),3)),"")

I've broken it down into pieces, and everything works correctly but one section.

 MATCH(1,([Agent]=[@Agent])*[COLOR=#ff0000][B]([Date]<=[@Date])*([Date]>=[@Date]-30)[/B][/COLOR]

It seems the issue is with the Match section. I need it to pull an array of calls offered on days between the date the agent was WFH and going back 30 days. I originally tried using an AND operator in the match function, but that didn't work. How can I specify this range of time in the formula?

Anyone have an idea how I can accomplish this? Or if there is an easier way

 

[Ron Coderre]

You're very welcome. I'm glad you figured it out.
(Crappy data can be so annoying! Power Query and its data cleansing capabilities have made my job so much easier!)