Plotting data on a line chart with a 2 month delay

[DaithiOK]

I am trying to plot a line chart but running into an issue with my expression.

My dimension is a variable called ReportMonth. The data I am trying to plot has a 2 month delay on it. For example the May data on the chart below is really from March. April is from Feb and so on. This is well known in the business and for this KPI everyone knows the data published each month is on a 2 month delay. 

The formula for this KPI in my table is as follows:

Sum(
{< 
VariousOtherFilters..., %ReportMonth1 = {$(vCurrentMonth)}, ForecastMonth = {$(vCurrentMonth-2)}}
>}
[Amount])

which essentially looks at the month of the report which is the current month and then takes the ForecastMonth which is 2 months ago. This works fine in a table as it only needs to look at one Report Month at a time. If i then change ReportMonth in my filter to April that would have vCurrentMonth = April and so ForecastMonth would be 2 months previous and so on. 

My problem is that in my line chart the ReportMonth is my dimension as below . In the line line chart my ForecastMonth filter obviously doesnt work as intended except for the current month as ReportMonth is seelcted in an external filter. If i select a previous month then that month on my line chart is correct. Below for example the May data is correct but the April data is not. The line should also extend back to Jan. 

[chrismarlow]

Hi,

As long as your dates behave I would probably try using above function similar to the below to try to get the offset working;

Cheers,

Chris.

[DaithiOK]

Oh this is interesting. Thanks for your response. I'm not sure how it would work though. In your example you are summing a column of values called forecast. In my data set the forecast refers to a month value and Date is not the 'Index' of the table so if i did Above() it would likely just take the amount from 2 lines above within the same category and not 2 months previously. 

Thanks for your response though I always learn something on these forums that I never knew.