Forum Discussion
Return Current or Latest if Current is not Available
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 ...
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
Super User
Super Usersupposing 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])
)
)
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