Forum Discussion

JeroenHD's avatar
JeroenHD
Icon for Helper I rankHelper I
4 years ago
Solved

LOOKUPVALUE using LASTNONBLANK use nearest value if blank

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 jo...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • JeroenHD's avatar
    JeroenHD
    4 years ago
    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]))
holmberg4's avatar
holmberg4
Regular Visitor
3 years ago

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=108488162365605006565&rtpof=true&sd=true