Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I need your help . Is there anyway to create a measure instead of calculated column for the case below . The results that I want to achieve are in columns USD INvoice value.
Scenario. I have a contract table where I need to calculate the USD invoice value based of the last valid rate of exchange.Thanks a lot
Rate of exchange table
Rate Id | Cyy from Code | Ccy to Code | Validate From | Validate To | Rate |
1 | EUR | USD | 27/10/2019 | 01/01/2099 | 1.2433 |
2 | EUR | USD | 29/10/2019 | 30/10/2019 | 1.24735 |
8 | GBP | USD | 29/10/2019 | 01/01/2099 | 1.4141 |
9 | GBP | EUR | 29/10/2019 | 01/01/2099 | 1.424 |
Contract | ccy | sttccy | Invoice value | USD INVOICE VALUE |
1 | EUR | 10 | 10 | |
2 | USD | USD | 10 | 12.433 |
3 | EUR | 10 | 12.433 | |
4 | EUR | EUR | 10 | 12.433 |
5 | USD | 10 | 10 | |
6 | GBP | 10 | 14.141 |
Solved! Go to Solution.
Please check if this is correct (In particular table the column 'Code FromTo' highlighted in red):
I've attached a PBIX file for you.
Proud to be a Super User!
Paul on Linkedin.
Sorry, can you clarify what the expected result is?
Regarding the second table (expected result I take it):
The column headings are confusing (at least to me). And what is the correlation between the "Invoice Value" and the "USD INVOICE VALUE"?
I may well be misunderstanding the table, but you have 2 row for EUR with different factors/outputs, a USD row with a the same factor as a EUR row, the GBP is applying one of the possible rates (but how is it specified? Both GBP rows in the rates table have the same date range...
I guess what I need to understand is which conversion rate is expected for each row.
Proud to be a Super User!
Paul on Linkedin.
Hi Paul,
We should take the last valid rate which in this case is valid to 01/01/2999 and we need to convert Invoice Value to Invoice Value in USD .
Hope it helps 🙂
Fact Contract.ccy as A Fact Contract.SttCcy as B Fact Contract.InvoiceValue as C FX Rate.Rate as D FX Rate.ValidDateTo as E Case when (A = USD & B = USD OR BLANK) Then USD (no conversion) when (A <> USD & B IS BLANK) Then (C * D where E = '01/01/2099') when (A = USD & B <> USD) Then (C * D where E = '01/01/2099') when (A <> USD & B <> USD) Then (C * D where E = '01/01/2099') when (A <>USD & B =USD) Then (C * D where E = '01/01/2099') |
Thanks, that helps. So in essence, if the sttccy column is Blank, we should take it a being USD, correct?
(I'm still confused about rows 1, 2, 3 & 4 however):
Contract ccy sttccy Invoice value USD INVOICE VALUE
1 | EUR | 10 | 10 | |
2 | USD | USD | 10 | 12.433 |
3 | EUR | 10 | 12.433 | |
4 | EUR | EUR | 10 | 12.433 |
5 | USD | 10 | 10 | |
6 | GBP | 10 | 14.141 |
Proud to be a Super User!
Paul on Linkedin.
Hi Paul,
Thanks again for replying.
The correct results shoud be :
Contract ccy sttccy Invoice value USD INVOICE VALUE
1 | USD | EUR | 10 | 12.433 |
2 | USD | USD | 10 | 10 |
3 | EUR | 10 | 12.433 | |
4 | EUR | EUR | 10 | 12.433 |
5 | USD | 10 | 10 | |
6 | GBP | 10 | 14.141 |
Thank you so much 🙂
Please check if this is correct (In particular table the column 'Code FromTo' highlighted in red):
I've attached a PBIX file for you.
Proud to be a Super User!
Paul on Linkedin.
OMG !!!! Looks perfect !!!
Thank you so much !!!
I've just realised you were asking for measures. Please note that the values for 'FromTO' and 'Code FromTo' are actually calculated columns. Do you need these as measures too? (especially the 'Code FromTo' which I have included in red in the sample table - if the final table is actually built from other tables, we need to make this a measure. I take it the rate table is an actual table in the model, so the calculated column 'FromTO' will work in the table itself)
They can be converted easily if need be.
Proud to be a Super User!
Paul on Linkedin.
Hi Paul,
Please see my commenst below ;
ve just realised you were asking for measures. Please note that the values for 'FromTO' and 'Code FromTo' are actually calculated columns. '>>>> If you can transform them in a measue ,I REALLY appreciate it :).
SUPER Good Night for you !
Ester
Here is a new version without the calculated columns. Please note that I have had to rewrite most of the measures, so reference the new measures in the file attached
New PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
It is perfect .Thank you so much again!!!! It couldnt be better 🙂
@macwhe , yes you can but you need have date in second table
Hi Amit,
Thanks for you rquick response.
Do you think it is possible to add RAte as Dimension table . Then we will have a relationship between sttcy (contract table) and ccy from code (Rate Table)?
And when you mentioned to add date in second table , did you mean add date in Contract table? in case we have the date in second table how would be the measure?
Thank you so much !
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |