Return Current or Latest if Current is not Available

[Anonymous]

Hi, 

I have a monthly FX table. 

And on my fact table, I want to lookup the monthly FX. For future Delivery Date (ie. Dec 2022 and Jan 2023), I want to return the latest month (ie. Nov 2022) FX amount as shown below. 

Please help. 

Thank you,

Jeff

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in Table 2

FX for calc. = lookupvalue('Table 1'[Fx],'Table 1'[Calendar]Date],calculate(max('Table 1'[Calendar Date]),filter('Table 1','Table 1'[Calendar Date]<=earlier('Table 2'[Delivery Date])))

Hope this helps.

[FreemanZ]

supposing the two tables are related, try add a column like

FX =

IF(

    [Date]<=TODAY(), RELATED(Table1[CalendarDate]),

    CALCULATE(

        MIN(Table2[FX]), Table2[DeliveryDate]=MAX(Table1[CalendarDate])

)

)

[Anonymous]

Thank you @FreemanZ for your replay. It does not work as expected. 

Due to other reasons, two tables are not linked and today() may cause other issue in the future. I slightly twisted your sugguestion a bit. 
FX=

IF(

    Table2[Delivery Date] <= max (Table1[Calendar Date]),

    LOOKUPVALUE(Table1[FX], Table1[Calendar Date], Table2[Delivery Date]),

    CALCULATE( MIN(Table1[FX]), Table2[Delivery Date] = MAX(Table1[Calendar Date]))

)

The IF's 3rd argument returns the 1st (earliest) row on Table1, which is 1.3319 in my example. 

Regards,

Jeff