Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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! Go to Solution.
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...
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |