Display previous year price when drill down on fiscper

data-excel.pngHi HANA Gurus,

I have a requirement to calculate Price Variance of actual and forecast price based on previous year price.

The issue i am facing is, the moment i drill down on fiscper for current year (2018), data in Previous year price column is displayed as NULL (it is expected behavior, since when we drill down on periods of 2018, previous year price i.e., is of 2017 will be blank ).Due to which Price Variance column has issue.

My requirement is to display previous year price (45 in below example) across all rows of 2018 fiscper, so that calculation of Price Variance will be fixed.

Time reference should be dynamically picked as we move to next period or next year.

PFB formula explaining requirement: (refer attachment for excel screenshot).

Formula: Price Variance = (D5-E5)/E5 for actuals till date (i.e., 8.2018) Price Variance = (D5-F5)/F5 for Forecast from future month (i.e., 9.2018)
Pre_Year_Avg_Price

45 (year 2017)

PFA excel data sample.

Thanks,

Rajiv