Calculated New Currency column based on two tables

[CanberkAcikbas]

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)

[ITManuel · Accepted solution]

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

[CanberkAcikbas]

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

[AnalyticPulse]

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

[CanberkAcikbas]

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

[AnalyticPulse]

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