Calculate from filter value
The problem I am trying to solve:
User selects a range of months for provider productivity. Each provider has a $ Goal associated with them. There is a Year to Date Goal column that can change based on what they select. Example: Jan-Mar has YTD Goal for March, where Feb-Jun has YTD Goal for Jun.
I've tried a lot of different ways to figure this out but nothing has worked so far. The last thing I tried was to have the user enter a Start month and an End month, then I do a max on Month and multiply that by the base goal to get the YTD Goal (ex. goal is 1,000, End Month is July so Goal YTD is 7,000).
Then I found that some providers don't have services yet for that month. If they are missing July data, the YTD goal is multiplied by 6 for that provider instead of 7.
If I could calculate based on the End Month instead of the individual provider, then my YTD Goal would be correct for everyone. Open to other suggestions if I am just missing something.
Provider A--------YTD Goal
Provider B--------YTD Goal