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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
CanberkAcikbas
Regular Visitor

Calculated New Currency column based on two tables

Calculated New Currency column based on two tables
 

First table consists of

 

Value of an Item(integer) like 100

Currency (string) like EUR , PLN, SEK

Invoice date (date) like yyyy/mm

 

Second table consists of

 

MON_UNIT_COD (string) and a constant as EUR

MON_UNIT_COD_1 (string) and can have various values like BGN, TRY, CYP, CZK however not EUR

DAT_END as end date  like yyyy/mm

EXCH_RATE as exhange rate like 1.95, 20.21 etc

 

 

 

What I need is a new column on the first table based on a conversion,

The first table consist of values in various currencies but we need a constant EUR conversion in the new column

But including these matches:

 

 

1-InvoiceDate = DAT_END

2- EXCH_RATE * VALUE should be the new value of this new calculated column

3- First table also consists of EUR values so there will be no conversion, so it should stay the same. (I could not formulize this part as well)

 

1 ACCEPTED SOLUTION
ITManuel
Responsive Resident
Responsive Resident

Hi @CanberkAcikbas ,

 

you can try the following code for a new calculated column in Table 1. 

AmountEUR = 
VAR _XChangeRate =
    LOOKUPVALUE (
        Table2[XChangeRate],
        Table2[COD1], Table1[Currency],
        Table2[Date], Table1[InvoiceDate]
    )
VAR _Result =
    IF ( Table1[Currency] = "EUR", Table1[Item], Table1[Item] * _XChangeRate )
RETURN
    _Result

 

Br

View solution in original post

5 REPLIES 5
ITManuel
Responsive Resident
Responsive Resident

Hi @CanberkAcikbas ,

 

you can try the following code for a new calculated column in Table 1. 

AmountEUR = 
VAR _XChangeRate =
    LOOKUPVALUE (
        Table2[XChangeRate],
        Table2[COD1], Table1[Currency],
        Table2[Date], Table1[InvoiceDate]
    )
VAR _Result =
    IF ( Table1[Currency] = "EUR", Table1[Item], Table1[Item] * _XChangeRate )
RETURN
    _Result

 

Br

Thank you so much for your quick responses, it worked perfectly fine !

AnalyticPulse
Super User
Super User

create relationship in your table and then try to use below dax:

NewColumn =
VAR CurrentCurrency = 'FirstTable'[Currency]
VAR ExchangeRate =
IF(
CurrentCurrency = "EUR",
1,
CALCULATE(
MAX('SecondTable'[EXCH_RATE]),
'SecondTable'[MON_UNIT_COD_1] = CurrentCurrency,
'SecondTable'[DAT_END] = 'FirstTable'[InvoiceDate]
)
)
RETURN
'FirstTable'[Value] * ExchangeRate

If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/

subscribe to Youtube channel For fun facts:
https://www.youtube.com/@CogniJourney

Thank you so much for your quick responses, it worked perfectly fine !

I dig a subscribe if you like my content. Shoot a sub to a channel  @CogniJourney
Thanks.😊

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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