Forum Discussion
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)
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 _ResultBr
5 Replies
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] * ExchangeRateIf 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/@CogniJourneyThank 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.😊
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 _ResultBr
Thank you so much for your quick responses, it worked perfectly fine !
