cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DSArkphire
Frequent Visitor

Slicer which can change currency using an exchange rate which varies based on date

I have a table which contains multiple exchange rates these vary in terms of currency and date updated. Ultimately I want to create a measure which will allow me to convert values dynamically based on the currency I select in a filter. The exchange rates used to convert my values will in turn by based on the actual document date.

 

If a value in document date is more recent then a corresponding value within “DateOfExchangeRateChange” it should go to the next most recent date and if document date is more recent then the most recent value in “DateOfExchangeRateChange” it will instead use that last value.

 

visual.pngtable.png

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hi @DSArkphire ,

I created a sample that can be calculated at the current exchange rate with the selected date, or if it is not selected, based on the exchange rate of the latest date.

 

  1. Create a calculated table.

 

Table = CALENDARAUTO()

1.png11.png

 

  1. Create a measure

 

Measure = 
var a = SELECTEDVALUE('Table'[Date])
var b = CALCULATE(SUM(Table1[$ExchangeRate])*SUM(Table2[currency]),
FILTER(Table1,Table1[DateOfExchangeRateChange] = a))
return
IF(ISFILTERED('Table'[Date]), b,
CALCULATE(SUM(Table1[$ExchangeRate])*SUM(Table2[currency]),
FILTER('Table','Table'[Date] = MAX(Table1[DateOfExchangeRateChange]))))

2.jpg21.jpg

 

Best Regards,

Xue Ding 

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

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

View solution in original post

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

Hi @DSArkphire ,

I created a sample that can be calculated at the current exchange rate with the selected date, or if it is not selected, based on the exchange rate of the latest date.

 

  1. Create a calculated table.

 

Table = CALENDARAUTO()

1.png11.png

 

  1. Create a measure

 

Measure = 
var a = SELECTEDVALUE('Table'[Date])
var b = CALCULATE(SUM(Table1[$ExchangeRate])*SUM(Table2[currency]),
FILTER(Table1,Table1[DateOfExchangeRateChange] = a))
return
IF(ISFILTERED('Table'[Date]), b,
CALCULATE(SUM(Table1[$ExchangeRate])*SUM(Table2[currency]),
FILTER('Table','Table'[Date] = MAX(Table1[DateOfExchangeRateChange]))))

2.jpg21.jpg

 

Best Regards,

Xue Ding 

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

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I need to do my requirement similar to this requirement but not able to get values by using given formula,

 

Can you pls see my details and do the needful pls , It would be great help if you can suggest on below 

I need to show the data always 6 years of data from 2015  - 2021 till currency year dynamically

 

User will select the year slcier selection and based on the year selection actualcost will be multplied wiht the selected year currency rate  dynamically for all the year

 

Ex : if user select 2015 year in selection, 2015 rate will be applicable for all the 6 years ( cost should be multiplied with 2015 currency rate and if select 2017 then 2017 rate should be multiply with cost for all the 6 years

 

Matrix to be shown like below ( years will get changes dynamically in matrix and will be shown only 6 years back from currency year)

Basiness   location  2015   2016  2017  2018  2019  2020 2021

 

I have one data set with below columns :

 

Business

Locationid

year

actualcost

currency id

exchange rate (with year wise)

 

no date in my dataset , only yearly and location base currency exchange rate are there in data set

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors