I have a combo graph detailing 2 types of sales across a 52 week period for the last 3 years. I have created an expression to show the percentage of sales so far this year of last year (Sum(Year3SalesIn)/Sum(Year2SalesIn)).
However, I am also trying to create an expression in the footnote to show the target sales for this point in the year to give the figure some context. For example, we are currently in week 21 of 52, therefore sales should be around 40% of last year in theory.
I have opted to write an if statement as follows: =If(week (today (1))<rangemax(Week), Week(today (1)) / rangemax(Week), 100). I've intended to structure it so that If the current week is smaller than the maximum week number in the selection it divides the current week number by the number of weeks selected. If the current week number is greater than or equal to the maximum week number of the selection, it returns 100 as we the target for that period would have been 100% of the last period and it is now over and closed.
My formula isn't working, and I can't even get the rangemax function alone to return the maximum week number contained in my selection.
I am also aware this wouldn't work if weeks were selected that are not continuous, so would also prefer to have a count of the number of weeks instead of taking the maximum number of weeks.
Any help would be much appreciated.