Extrapolation using data from MS Access table

[naveen73]

Hi all,

I have the following data from an MS Access table is as below:

I want to extrapolate in Power Query.

• Every month I have end-of-day values
• I want to pass the date values from my Excel cells: H1-J1
• The extrapolation is simple: take the difference between two end of month values and divides this by the number of days between the end of month dates. Then multiple this by the difference between the last end of month and the date of which the extrapolation needs to be performed.
• When the date in H1-J1 is after the date in column C the value should be zero.
• The data will be updated every month as it adds new data to the table for each new month.

Please see links to MS Access and MS Excel files.

https://docs.google.com/spreadsheets/d/1HpTgrvfwqjncsuCN9UbojMsRS5tWw0Hl/edit?usp=sharing&ouid=11029...

https://drive.google.com/file/d/1-IHPBZpk_lauermdYjSXUlUsyU5m-mF4/view?usp=sharing

thanks,

Naveen

[lbendlin]

I said it before and I'll say it again - Feb 28 is not EOM ! Please correct your sample data.

[naveen73]

Hi,

Thanks for this.  I updated the data in the file. Click on the link to download.

Thanks,

Naveen