Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
macwhe
Helper I
Helper I

Measure instead of calculated column in order to calculate Value in USD

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 IdCyy from CodeCcy to CodeValidate FromValidate ToRate
1EURUSD27/10/201901/01/20991.2433
2EURUSD29/10/201930/10/20191.24735
8GBPUSD29/10/201901/01/20991.4141
9GBPEUR29/10/201901/01/20991.424

 

 

ContractccysttccyInvoice valueUSD INVOICE VALUE
1EUR 1010
2USDUSD1012.433
3EUR 1012.433
4EUREUR1012.433
5USD 1010
6GBP 1014.141
1 ACCEPTED SOLUTION

@macwhe 

 

Please check if this is correct (In particular table the column 'Code FromTo' highlighted in red):

 

Result.JPG

 

I've attached a PBIX file for you.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

12 REPLIES 12
PaulDBrown
Community Champion
Community Champion

@macwhe 

 

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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')


@macwhe 

 

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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

1USDEUR1012.433
2USDUSD1010
3EUR 1012.433
4EUREUR1012.433
5USD 1010
6GBP 1014.141

Thank you so much 🙂

@macwhe 

 

Please check if this is correct (In particular table the column 'Code FromTo' highlighted in red):

 

Result.JPG

 

I've attached a PBIX file for you.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






OMG !!!! Looks perfect !!! 

 

Thank you so much !!!

 

 

@macwhe 

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

@macwhe 

 

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

Measure Model.JPG

 

New PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






It is perfect .Thank you so much again!!!! It couldnt be better 🙂

 

 

amitchandak
Super User
Super User

@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 !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.