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

[ravjay]

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,

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

[tamerj1]

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 ) 

[ravjay]

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]

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

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.

[tamerj1]

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

[Anonymous]

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.