Currently we are looking to calculate the average number of calls per day, based on If Else conditions.
We want to start calculating the average calls from March 3, 2020 until March 31, 2020.
And also have the average calls of the other months starting from the 1st day of the month to the last day. I'm using Today().
But when we apply a filter for a past month, like March, the formula breaks, I was thinking in using Max(Call_Date) but it doesn't work if an Agent doesn't continue making calls during the following days of the month. I know there is a feature we could use, like a Master Calendar, but I don't know if this could help.
=Avg(if(Max(Month([Leave Time])) = '3'
,Count(distinct[Call Name]) / NetWorkDays(Date('03/16/2020','mm/dd/yyyy'),Date('03/31/2020','mm/dd/yyyy')),
if(Not(Max(Month([Leave Time])) = '3')
,Count(distinct[Call Name]) / NetWorkDays(MonthStart(Today()),Today())
)) ,[Call Name] ))
Could you please help me understand how we can solve the issue? Maybe there is a feature like a Master Calendar that could help.