If value is blank, find last record and multiple it by 1.035 and apply to all preceding blanks

[rachaelwalker]

Every two weeks we capture forecasted revenue to determine accuracy but we also use it to forecast out until end of year. I am looking for a formula that calculates a projected revenue for any date that is not yet captured by finding the last record value and multiplying it by 1.035 and to continue to apply it to all rows. This is very easy to do in excel but I cannot wrap my head around it in power bi.

I have two tables 'Forecast Calendar' with [Date]. I have a 'Forecast Audit' table that has the [Date] & [Projected Revenue]. I added index columns to both thinking this may lead me in the right direction. I also merged the tables in power query.

dataset: https://docs.google.com/spreadsheets/d/1Xghbz_vf8VXOcMJCT2zFZu1ekpnLhzQvG4cSDsfBDEE/edit?usp=sharing

here is a snippet as well. 

Expected results (highlighted yellow. I want it to show the actual $$ value but wanted to show the excel formula I would use if in excel)

*ignore the date format

[Ashish_Mathur]

Hi,

Share the download link of the file with the 2 original tables (not the one you merged in Power Query)

[rachaelwalker]

Forecast Audit: https://drive.google.com/file/d/1h6SP5TyHtPDF7kQsUZTZQ4kLos3SiNcY/view?usp=sharing

Forecast Calendar: https://drive.google.com/file/d/14dzEbl-gd_BoW98IcUgeEVO6h4Fqufbx/view?usp=sharing

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[rachaelwalker]

I need it to be accumulating so I need 4/12 date to be (53,360,016.83 * 1.5) and so on. Here are the values I am looking for. Thank you for taking the time to help!

[Ahmedx · Accepted solution]

try the code in M language

[rachaelwalker]

Thank you so much this worked perfectly!!