cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Multi-exchange rate Conversion (Fixed rate by different time)

Hi, I am working on a multi-exchange rate Conversion in my model. There are 2 critiria. 

1.) Multi-currency to Multi-currency

2.) Exchange rates are fixed in different time (i.e 0.67/AUS in 2019, 0.72/AUS in 2020)

The SUM value will be based on the exchange rate at that period of time.

 

I'm currently using the DAX found here as below. 

 

Measure 2 = 
var _maxdate=TOPN(1,FILTER('FX table','FX table'[Delivery Date]<=MAX('Table'[Delivery Date])),'FX table'[Delivery Date],DESC)
Return
SUMX(_maxdate,[Exchange Rate])*CALCULATE(SUM('Table'[Sales Price]))
Measure 3 = SUMX('Table',[Measure 2])

 

 

It works, however, when I am trying to add one more table to cacalute the other converted Price. I found them not possible to have relationship with both 1.)FX table & 2.)Date table at the same time.

Do you have any idea how it could be possble linking them to a "Date table" to make a Date slicer in this situation?

Great thanks!!

 
 

Example.pbix

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Multi-exchange rate Conversion (Fixed rate by different time)

Hi @ngct1112 ,

When creating an active relationship between Date table and Table, an inactive table relationship between Date table and Table2,

what is your expected output? Like the below picture or anything else?

realationship.png

If this is your expected output, you can use Userelationship() to modify the [Converted2] measure and set it as not blank in the visual filter.

Total Price(Converted2) = 
CALCULATE(
    SUMX('Table2',[Net Price(Convert2)]),
    USERELATIONSHIP(Table2[Delivery Date],'Date Link'[Date])
)

visual filter.png

 

Best Regards,
Yingjie Li

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

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Multi-exchange rate Conversion (Fixed rate by different time)

Hi @ngct1112 ,

Based on your description, seems not certain what is your expected ouutput.

"Do you have any idea how it could be possble linking them to a "Date table" to make a Date slicer in this situation?"

I have checked your previous post issue and found that you have created relationship between date table and table/table 2. Why not create the relationship based on the FX table and the date table directly?

relationship.png

 

Best Regards,
Yingjie Li

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

Highlighted
Helper V
Helper V

Re: Multi-exchange rate Conversion (Fixed rate by different time)

Hi @v-yingjl , I am hoping that there could be a Date Table (as a slicer) to control "Table" and "table2" or more. 

However, it seems not possible to do the relationship in this situation.

ngct1112_0-1601275959713.png

 

If the "Date Report" links to the "FX Table" as suggested, when filtering the "Financial Year", it is filtering the year of FX date but not the "delivery date". The result could be different.

ngct1112_3-1601277191607.png

 

ngct1112_2-1601277004804.png

 

In this case, when trying to SUM the price delivered in FY2018, (JPY 11,110) should not be calculated.

 

Appreciated if there are any ways to calculate with fixed FX% in this scenario.

 

 

Highlighted
Community Support
Community Support

Re: Multi-exchange rate Conversion (Fixed rate by different time)

Hi @ngct1112 ,

When creating an active relationship between Date table and Table, an inactive table relationship between Date table and Table2,

what is your expected output? Like the below picture or anything else?

realationship.png

If this is your expected output, you can use Userelationship() to modify the [Converted2] measure and set it as not blank in the visual filter.

Total Price(Converted2) = 
CALCULATE(
    SUMX('Table2',[Net Price(Convert2)]),
    USERELATIONSHIP(Table2[Delivery Date],'Date Link'[Date])
)

visual filter.png

 

Best Regards,
Yingjie Li

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

Highlighted
Helper V
Helper V

Re: Multi-exchange rate Conversion (Fixed rate by different time)

@v-yingjl , this is exactly what I have been trying to do. it work wonderfully. Appreciated so much with your help!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors