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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors