How to SUMX value with Fixed Exchange%

[ngct1112]

I am looking for help for with the problem I still could not fix.

I am trying to SUMX the total value with exchange rate, in accordance with DATE.

Refer to the FX table, In this case, the total Sales Price should be: 1110.4 x 115 + 2.4 x 117 = 127976.8

(it takes '115' when the sales date after 1/8/2019 and take '117' when sales date between 1/7/2018 & 1/8/2019)

Table

Delivery Date	Sales Price	Currency
6/5/2020	11110.4	USD
8/4/2019	2.4	USD

FX table

To Currency	Exchange Rate	Delivery Date	From Currency
JPY	115	1/8/2019	USD
JPY	117	1/7/2018	USD

I can do the multi-conversion of currency but cannot figure out how to cretiria the DATE. Please see the raw data and attached PBIX Conversion with FX.Pbix 

Appreciated if I could find any help.

Currency Format = LASTNONBLANK ('Currency Format'[Format], 1 )

FixedFXTotalPrice = SUMX('Table','Table'[Sales Price]*
	IF([Currency Format]='Table'[Currency],"1",
        (Lookupvalue('FX Table'[Exchange Rate],
		'FX table'[From Currency],'Table'[Currency],
			'FX Table'[To Currency],[Currency Format]))))

Here is the raw data,

FX table

To Currency	Exchange Rate	Delivery Date	From Currency
JPY	115	1/8/2019	USD
HKD	7.75	1/8/2019	USD
JPY	1	1/8/2019	JPY
HKD	0.03	1/8/2019	JPY
JPY	117	1/7/2018	USD
HKD	7.76	1/7/2018	USD
JPY	1	1/7/2018	JPY
HKD	0.035	1/7/2018	JPY

Table

Delivery Date	Sales Price	Currency
6/5/2020	11110.4	JPY
8/4/2019	2.4	USD
12/10/2018	5112	JPY
10/8/2020	920.1	USD

Currency Format

Format	FormatName	Index	New_Format
HKD	HKD	6	$#,##0;($#,##0)
JPY	JPY	8	¥#,##0;(¥#,##0)
USD	USD	13	$#,##0;($#,##0)

[FarhanAhmed]

You need to create List of Dates with currency rate.

You can do it by

- Group your table with "To Currency", "From Currency" & "Exchange Rate" with MIN & MAX of delivery dates

- After that convert Min & Max dates to "WholeNumber"

- Add New column "List Dates" using "{[MinDate]..[MaxDate]}"

- Remove Min & Max Dates columns

- Expand "List Dates" to new rows.

- Change the datatype back to "Date" and you will have your Exchange rates for each date , that will help you resolving your query.

If this answer help you, Please mark it as Solution and don't forget to give Kudos too.

[ngct1112]

@FarhanAhmed I have tried to follow but failed. Is it possible you could provide the script with the steps?

Appreciated

[v-kelly-msft]

Hi  @ngct1112 ,

Based on your raw data,could you pls advise me the expected output?And how to calculate it out?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ngct1112]

@v-kelly-msft 

I would like to add a slicer on the page using "To Currency" From FX Table

The total calculation is sum by "Sales Price" from Table base on its Delivery Date's FX rate,

When choose JPY in the slicer:

= (11110*1) + (2.4*117) + (5112*1) + (920.1*115) = 122314.3

When choose HKD in the slicer:

= (11110*0.03) + (2.4*7.76) + (5112*0.035) + (920.1*7.75) = 766.619

Do you think it is possible to handle this situation? Great thanks.

Table:

Delivery Date	Sales Price	Currency
6/5/2020	11110	JPY
8/4/2019	2.4	USD
12/10/2018	5112	JPY
10/8/2020	920.1	USD

FX Table:

To Currency	Exchange Rate	Delivery Date	From Currency
JPY	115	1/8/2019	USD
HKD	7.75	1/8/2019	USD
JPY	1	1/8/2019	JPY
HKD	0.03	1/8/2019	JPY
JPY	117	1/7/2018	USD
HKD	7.76	1/7/2018	USD
JPY	1	1/7/2018	JPY
HKD	0.035	1/7/2018	JPY

[v-kelly-msft · Accepted solution]

Hi  @ngct1112 ,

Sorry for the late reply!

Create 2 measures 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])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ngct1112]

Hi @v-kelly-msft , may I have a little further question on this solution?

This solution has been working very well. Yet, 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 as below

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!!

https://drive.google.com/file/d/1zw0ZHpCDoo0ucQzrvcQADN1Nr_9iF96g/view?usp=sharing

[ngct1112]

@v-kelly-msft , I have tried few testing on my current model and it works smoothly and acuately.

And the formula you suggested could even convert FX% according to >2 designated Date.

Appreciated with your help!

[amitchandak]

@ngct1112 , You can create a new column in first table like

JPY Amount= maxx(filter('FX table', 'FX table'[From Currency] = Table[Currency] && 'FX table'[Delivery Date] <Table[Delivery Date] && 'FX table'[To Currency] ='JPY'),
lastnonblankvalue( 'FX table'[Delivery Date], max('FX table'[To Currency])))

[ngct1112]

@amitchandak  I have create the relationship for 'Table' and 'FX Table' but I am still not able to put the new column in the table. Could you please give more advice. Appreciate with it.