Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JeroenHD
Helper I
Helper I

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 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.

 

Screenshot 2022-05-25 130822.png

 

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

1 ACCEPTED SOLUTION
JeroenHD
Helper I
Helper I

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]))

View solution in original post

2 REPLIES 2
holmberg4
Regular Visitor

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
Helper I
Helper I

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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.