LOOKUPVALUE using LASTNONBLANK use nearest value if blank

[JeroenHD]

Hello Team, 

First i wanted to use LOOKUPVALUE, but then somebody suggest  to use the LASTNONBLANK. Which worked to fill the GBP rate column. The Lookup column is the RateName column. This is a joined string of 4 values with a year and a month at the end. 

Values on the table where GBP Rate derives from may be updated during a month. Or in some case not at all. Which obviously leaves me with blanks. 

In case a blank is returned for WIJGBO20225 in this case i would like it to look at WIJGBP20224 and use that value. 

How can i obtain that? Below is the dax formula i came up with.

GBP Rate =
IF (
PAYABLE_FREIGHT[SITE_ID] = "COV",
1,
CALCULATE (
LASTNONBLANK ( JHD_Service_Dashboard_Currency[BUY_RATE], 1 ),
FILTER (
ALL ( JHD_Service_Dashboard_Currency ),
JHD_Service_Dashboard_Currency[Rate_name] = PAYABLE_FREIGHT[RateName]
)
)
)

Any help is appreciated.

Kind regards,
Jeroen

[holmberg4]

Dear JeroenHD and community,

I am currently working on a similar problem of generating a new measure to report sales on a different currency. Exchange rate data is published with some delay and not published at all at certain random dates. I have tried to use what you have shared here but is does not work for me. I am sharing the formula and some sample data including a sheet where my problem is signaled in color yellow.
Thanks for your help in advance!   

Turnover_USD =
VAR _date = MAX(DateTable[Date])
VAR _Sales = Sum(Sales AR$)
VAR _rate = IF(
LOOKUPVALUE(
Exchange_rate_table[USD ex.rate.]
,Exchange_rate_table[Date] ,_date
) = BLANK()
,LASTNONBLANK(Exchange_rate_table[Date],calculate(AVERAGE(Exchange_rate_table[USD ex.rate.])))
,LOOKUPVALUE(Exchange_rate_table[USD ex.rate.], Exchange_rate_table[Date] ,_date
))
VAR _salessusd = DIVIDE(_Sales,_rate)

RETURN
_salessusd

Example file = https://docs.google.com/spreadsheets/d/1vVTUBPmKUTWfQ6aUufCG2j_cBWn_06-D/edit?usp=share_link&ouid=10...

[JeroenHD · Accepted solution]

Solved it by using:

if (LOOKUPVALUE(JHD_Service_Dashboard_Currency[Rate_name],JHD_Service_Dashboard_Currency[Rate_name],PAYABLE_FREIGHT[GBP Rate Name]) = "",LASTNONBLANK(JHD_Service_Dashboard_Currency[Rate_name],calculate(sum(JHD_Service_Dashboard_Currency[BUY_RATE]))),LOOKUPVALUE(JHD_Service_Dashboard_Currency[Rate_name],JHD_Service_Dashboard_Currency[Rate_name],PAYABLE_FREIGHT[GBP Rate Name]))