Hi All - I'm using a formula to track out budget vs. actual by week of month (see below) and now I'm looking to roll it back to just track month over month using the same formula, but replacing the Calendar Week of Year value with something else, but I can't seem to figure out what that other value is. I've tried a few different things. Any help would be appreciated! Thanks so much.
Current Formula with Week-to-Week Tracking - is working!
CASE WHEN [Max Calendar Week Of Year] = 27 THEN 5041
when [Max Calendar Week Of Year] = 28 THEN 6979
when [Max Calendar Week Of Year] = 29 THEN 6763
when [Max Calendar Week Of Year] = 30 THEN 6092
Current Formula with Month-to-Month Tracking - needs help! I think it's the bolded value below that I have to change but can't seem figure it out.
CASE WHEN [Total Calendar Month] = 7 THEN 2904
when [Total Calendar Month] = 8 THEN 29957
END
Thanks so much!
- M
I think it's the case that you have to use MAX or MIN with these types of formulas. Have you tried using MAX(Calendar Month of Year)?
It worked! I was typing the formula rather than clicking through as I went and that seemed to do the trick, oddly enough. Thanks for the second eye, Nate!
Great! Glad to be helpful.