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

[ngct1112]

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

[v-yingjl]

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?

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.

[ngct1112]

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.

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.

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.

[v-yingjl · Accepted solution]

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?

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

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.

[ngct1112]

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