Always show the latest Rate

[tan_chungkam]

Hi all,

I have a excel table as below:

Table

I would like to do in scripting level to always show it's latest exchange rate.

Please find below as my expected outcome:

Expected Outcome

[jonathandienst]

Use a pattern like this to fill in missing rates with the most recent:

LOAD Year,
	Period,
	[Base Currency],
	If([Base Currency] = Previous([Base Currency]), 
		Alt(Rate, Peek(Rate)),
		Rate) as Rate
FROM ...
Order by [Base Currency],Year, Period;

 Please post your data as a text table rather than an image. That allows for testing and validation of the solution.

[Channa]

i think he is looking for single row 

where year =year(today()) and month=max(month)

[tan_chungkam]

@jonathandienst ,

Thanks for the help.

However, it doesn't work on it.

What I'm looking for is the previous rate should always same as the rate of  the latest year period.

For e.g. AUD rate in Year (2019) &  Period (1) is 1.2. When it comes to Year (2019) & Period (2), the AUD rate is 1.3. Thus, the AUD rate in Year (2019) & Period (1) should change to 1.3.

Please find attached as my excel file.

Really appreciate your time spending and work. Thanks a million.

[Channa]

irrespective of period selection..  it should show latest rate ??

is it correct?

[tan_chungkam]

hi @Channa ,

Yes, it is.

To put it in simply,  the result should be same as the above screenshot.

It mean the AUD rate was 1.617314 in Year 2019 Period 1 but it should be the latest rate - 1.5966 after the scripting.