Take max date with a value for actual and forecast comparison

[#1]

Hi!

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

Thank you for your help in advance!!!

[sunny_talwar]

So when you were doing this using selection, did you select all the months upto the actual month or just by selecting the max month, you were able to get the right %age?

[#3]

All Months up to the max month, i.e. I select from 01/01/2017 to yesterday, and that gives the correct answer.

Thanks for your question!

[sunny_talwar]

May be like this for forecast

Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max({<Actual = {'*'}>}Date)))"}>}Forecast)