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

      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 DateSales PriceCurrency
      6/5/202011110JPY
      8/4/20192.4USD
      12/10/20185112JPY
      10/8/2020920.1USD

       

      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
      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        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!