Forum Discussion

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

How to SUMX value with Fixed Exchange%

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 DateSales PriceCurrency
6/5/202011110.4USD
8/4/20192.4USD

 

FX table

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

 

 

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 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
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      ngct1112
      Icon for Post Patron rankPost Patron
      6 years ago

      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.

       

       

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      ngct1112
      Icon for Post Patron rankPost Patron
      6 years ago

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

      Appreciated

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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!