Forum Discussion

Regular Visitor

2 years ago

Solved

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

Need Help

ITManuel

2 years ago

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

AnalyticPulse

Solution Sage

2 years ago

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