Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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)
Solved! Go to 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
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 !
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.😊
User | Count |
---|---|
131 | |
71 | |
70 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |