Forum Discussion
Helper ILOOKUPVALUE 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 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
- 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]))
2 Replies
JeroenHDSolved 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]))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
