Take max date with a value for actual and forecast comparison
I currently have a line chart with two expressions over a date dimension, (1) Actual and (2) forecast sales data for 2017.
I want to show the current '% of forecast' (actual to date divided by forecast to date) in a text box above the chart. My problem is that by simply dividing actual sales by forecast, I have a whole years' worth of data (all of 2017) in the denominator, and only year-to-date actual data, creating an incorrect number.
This problem resolves if I filter the data correctly, however I'm after a text box which shows this accurately without filtering, and also updates when selecting just Jan, for instance (and displays Jan Actual divided by Jan Forecast).
I think I need to take the max date with a value, and have this set as a 'stop' so I don't over-divide.
I'm pretty sure this is possible with some Set Analysis, however I'm not sure about how to do this