Retrieving last non zero value for daily exchange rates

NicholasValentinus
NicholasValentinus
edited December 2022 in Modeling

Hi,

 

I have a daily exchange rate module which store the daily rates for multiple currencies. I wanted to get the last non-zero value for each date for example we have daily exchange rates for 1 Jan 2021 but none for 2nd and 3rd of Jan 2021, so the daily exchange rates for 3 Jan 2021 should display the rates of 1 Jan 2021 since the last non-zero value for daily exchange rates is available at 1 Jan 2021.

 

NicholasValentinus_0-1671607489646.png

 

Is there any workaround or solution for this requirement?

Any help is highly appreciated

Thank you!

Tagged:

Best Answer

  • nikmg
    nikmg
    Answer ✓

     

    HI @NicholasValentinus ,

     

    In your "Last Non Zero" line item you can write this formula to achieve this result.

    IF Daily Rates <> 0 THEN Daily Rates ELSE PREVIOUS(Last Non Zero)

     

    EX:

    nikmg_0-1671612820914.png

     

    Best,

    Nik

     

Answers

  • nmartinez
    nmartinez

    This would be extremely useful. yet the screen copy you made is not readable (too small) so i can't the formula and last non zero is not recognized. Could you provide the formula please

Categories