Phasing calculation or forecasting calculation with planned data

[DianaZ]

Hello,

I need some help and wondering if anyone could help. I have my sample data as follows and trying to achieve arranging the data and create measure to achieve the chart. Has tried this with a few ways but being unsuccessful. Something anyone could help and advice what would be the best way to approach?

Table 1

Date	YTD Actual	Target
31-01-2021	13000
28-02-2021	12800
31-03-2021	12720
31-12-2022		9000

Table 2

Date	Planned Reduction
30-04-21	100
31-05-21	200
30-06-21	400
31-07-21	100
31-08-21	210
30-09-21	100
31-10-21	200
30-11-21	260
31-12-21	400
31-01-22	50
28-02-22	50
31-03-22	100
30-04-22	150
31-05-22	150
30-06-22	200
31-07-22
31-08-22
30-09-22	150
31-10-22	150
30-11-22	200
31-12-22	50

Desired Outcome

Quarter/Year	Actual (Current Quarter)	Planned Reduction by Quarter	Cost Phasing	Forecast at 2022	YE 2022
Q1 2021	12720		12720
Q2 2021		700	12020
Q3 2021		410	11610
Q4 2021		860	10750
Q1 2022		200	10550
Q2 2022		500	10050
Q3 2022		150	9900
Q4 2022		450	9450	9450
Target					9000

[DianaZ]

Hi @Jihwan_Kim ,

Thank you so much for this and it has help tremendously! I have applied this into my actual work and it work like perfectly fine. Thank you again!

Regards,

Diana

[Jihwan_Kim · Accepted solution]

Hi, @DianaZ 

I am not sure why Q4 2022 planned reduction is 450 (400 ???), but please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/9u9tb7uml9jfavx/dianaz.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/