I have a variable called MonthNum which is the number of the month in a financial year, therefore July = 1, August = 2 etc.
I want to be able to look at the average of a set of data as well as the current months data side by side.
To do this I have a formula to work out the average as:
Avg(if(Year = vCurrentYear, Sales))
When a range of months is selected this gives the average but at the same time I want a formula that will display the most recent month sales in the month range I select. So if I choose MonthNum 1 to 8 then the average gives me an average of 8 months data and the current month gives me the data for MonthNum = 8
I could do it like this:
Sum(if(Year=vCurrentYear and MonthNum = 8, Sales) )
but then its stuck on month 8 until I change the formula.
If I add another expression and use
I get 8 as the answer, so I want to be able to add these together but I cannot nest a MaxString into a Sum function.
This doesnt work
Sum(if(Year = vCurrentYear and MonthNum = MaxString(MonthNum), Sales))