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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ravjay
Frequent Visitor

need help for foreign currency selection and converting values based on that

Hi,

 

I do have three tables,

1. FCRATE -  Multiple fields, contain different currencies, dates, and conversion rates for other currencies

2. Project cost - Three fields - Project name, project transaction amount ($), Transaction date

3. Date table

 

The relationship between tables is by Date. FCRATE & Project Cost table connected to Date table. 

 

I have a visual showing the Project cost for each project. Also have a filter to see Project cost by date, month or year.

Now, I need a new slicer, that allows users to select the desired currency from a drop-down. Once the user selects a currency, all amounts in visuals need to convert to that currency based on the currency conversion rate as of the selected date from the filter.

Other conditions,

if the user selected a desired month or year - then the average currency rate for that period should be calculated and converted to the project cost amount based on that.

 

My FCRATE table,

ravjay_0-1678332493905.png

 

Can someone guide me in creating key measures and slicers for this?

5 REPLIES 5
tamerj1
Super User
Super User

Hi @ravjay 
Are the rates available for every single date? Meaning if I select any date from the date table there will be a corrosponding exchange rate for all of the currencies in the FCRATE table?
The END_DATE of the FCRATE table seems to be of a text data type. Be aware that this needs to be of a DateTime data type.

If above two points are confirmed and the FCRATE[CODE] is placed as a slicer then project cost measure can be amended as

Project Cost Amount =
SUM ( 'Project Cost'[Transaction Amount] ) * SELECTEDVALUE ( FCRATE[USD], 1 ) 

Yes, rates are available for all days. 

- Each currency has conversion rates for all other currencies for each date. 

END_DATE field changed to date format 

ravjay
Frequent Visitor

Please also note that the structure of the FCRATE Table is as follows,

   

ravjay_0-1679461651603.png

Let's assume transaction amounts are in the XOF rate. 

 

So, if I select GBP on the slicer and the date 30/03/2020 from the date filter, What I'm expecting is the transaction amount will be changed from XOF to reflect the GBP amount for that date. 

 

For example,

1. if the transaction amount is 10,000 in XOF, once the slicer is set to GBP &  date filter is applied as 30/03/2020, the transaction amount should be changed to 13.5689

 

2. Let's assume transaction amounts are in the CAD rate. 

 if the transaction amount is 10,000 in CAD, once the slicer is set to USD &  date filter is applied as 30/03/2020, the transaction amount should be changed to 7,058.5172, 

 

likewise, the transaction amount needs to change based on the transaction currency, and the selected currency on slicer & date.

@ravjay 
My previous reply solution was based on the assumption that the Date table filters both 'Project cost' and FCRATE tables. If this is the case then I don't see why it shouldn't work.

In case a range of dates is selected, the following should work

Project Cost Amount =
SUMX (
    VALUES ( 'Date'[Date] ),
    CALCULATE (
        SUM ( 'Project Cost'[Transaction Amount] ) * SELECTEDVALUE ( FCRATE[USD], 1 )
    )
)
v-rongtiep-msft
Community Support
Community Support

Hi @ravjay ,

Could you please provide your pbix file without privacy information and desired output with more details?

 

How to Get Your Question Answered Quickly 

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.