how to show previous years , current year , Future years cost in cross table based on year drop down filter

[gurusai sankar]

Hi All,

i have the data like Program,Year (2017, 2018, 2019 , 2020, 2021, 2022), Cap cost Forecast, Exp Cost Forecast , Total Cost forecast (Cap cost Forecast+ Exp Cost Forecast) ,

similarly haveCap cost Actual, Exp Cost Actual, Total Cost Actual (Cap cost Actual+ Exp Cost Actual) ,

My requirement is - Based on Year dropdown selection i should show Previous years , Current year , Future years cost (Cap , Exp, Total) with respect to the program in cross table.

Ex: If we select 2019 in the drop down filter it should show previuos years cost (2017+2018), Current Year cost (2019) , future Years cost (2020+2021+2022) with respect to the program.

Note: For Previuos Years cost- it should consider the cost which is related to the Actuals and For current & future years cost - it should consider the cost which is related to the forecast.

Ex: previous years cost: if we select 2020in the filter - it should show the cost which is realated to the Actual cost of cap , exp , Total (it shows sum of actuals cost until last month(2019 Sep) + shows Forecast cost (oct + nov +dec 2019)- it should be the combination of both costs(the combimation of actuals & forecast) , this combination works only for current year not for other years.

Kindly help on his.

if you feel that it would be difficult to fix previous year cost , atleast help me on fixing current year & future years cost.

it would be very helpful to attach dxp file with some solution.

 

Thaanks in advance

 

Regards,

Guru.