Forum Discussion

ngct1112's avatar
ngct1112
Icon for Post Patron rankPost Patron
6 years ago

How to SUMX value with Fixed Exchange%

Hi, I am trying to SUM a value with multiple exchange% conversion and using the following formula.

 

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

 

 

It works. However, I would like to add one more cretiria that I hope the conversion of exchange% is based on date.

For example, I hope it will take '115' when the sales date after 1/8/2019 and take '117' when sales date between 1/7/2018 & 1/8/2019

To CurrencyExchange RateDelivery DateFrom Currency
JPY1151/8/2019USD
JPY1171/7/2018USD

 

 

Please help suggest how I could adjust the formula that it could calculate the Total Price with a Dynamic date.

 

Here is the raw data,

FX table

To CurrencyExchange RateDelivery DateFrom Currency
JPY1151/8/2019USD
HKD7.751/8/2019USD
JPY11/8/2019JPY
HKD0.031/8/2019JPY
JPY1171/7/2018USD
HKD7.761/7/2018USD
JPY11/7/2018JPY
HKD0.0351/7/2018JPY

 

Table

Delivery DateSales PriceCurrency
6/5/202011110.4JPY
8/4/20192.4USD
12/10/20185112JPY
10/8/2020920.1USD

 

Currency Format

FormatFormatNameIndexNew_Format
HKDHKD6$#,##0;($#,##0)
JPYJPY8¥#,##0;(¥#,##0)
USDUSD13$#,##0;($#,##0)

 

Need Help

4 Replies