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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bsz412
Helper III
Helper III

fixing DAX formula - exchange rate

Hi, 

 

I need to convert local currency amounts to EUR by using the exchange rate of the selected end-year and the last available month of that year. I have a formula that works, but it does not use the same ex rate for each month: 

first column is a date column from a date table

second is the local currency amount

3rd: result of my Vlookup ex rate conversion dax measure

4th: ex rate test: shows what ex rate the vlookup measure used

the last 2 columns are the result of 2 measures: 

End month number =
VAR _maxyear = MAX('year_selector'[year])
RETURN
CALCULATE(
    MAX(purchases_by_week_vendor[month]),
    ALL(purchases_by_week_vendor),
    purchases_by_week_vendor[StartYear]=_maxyear
    )
 
End year number =
    MAX('year_selector'[year])
 

bsz412_0-1674060092963.png

the 2 measures show the same, correct month and year in this table, however in the Vlookup dax measure these values are changing: 

 

Sell-in EUR Vlookup =
SUMX (
    purchases_by_week_vendor,
    VAR exchRate =
       
            LOOKUPVALUE (
            exchange_rates[avg_rate],
            exchange_rates[source_currency_code], purchases_by_week_vendor[currency_code],
            exchange_rates[year], MAX ( year_selector[year]) ,
            exchange_rates[month], [End month number]
        )
 
RETURN
         [Sell-in local currency (test)] * exchRate
)
 
The issue is, that the year_selection table is connected to the date table. I cannot inactivate that relationship as the datamodel is used for several reports and I cannot change that relationship. 
As I see the formula is correctly choosing the year, but the month is not consistent in the monthly breakdown. 
 
If selected year is 2022, latest available month in the fact table is November, so the 2022 nov ex rate should be used for each and every month. 
 
bsz412_1-1674060582024.png

 

Any idea how I could fix this formula?

thank you !

0 REPLIES 0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors