Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get exchange rate

Good afternoon.
I hope you can help me.
I have a table with the exchange rate in different currencies from 2019 to 2020 by months. This table will be updated each month with the value of the corresponding month. Below table image:

 
 

I need to get the CLPaMO (which is the exchange rate) for the current year (2020) and for the maximum month found in the column MES (month) = 9. The objective is for it to be sustainable over time so that when the information is updated in the coming months and years, can always obtain the exchange rate in the maximum month of the current year. The value of the CLPaMO will be used to multiply it by the sales of the company. I hope I have explained myself well.

I remain attentive and I will appreciate your support.
Cheers,
Richard

Need Help

7 Replies

  • S_JB's avatar
    S_JB
    Icon for Resolver III rankResolver III
    5 years ago

    There may be an easier way to do this but one potential solution is to create 3 columns;
    1. Sum anio and mes

    2.  Select the maximum value from the anio and mes sum

    3. Create a flag as to whether the sum of anio and mes equals the maximum anio and mes

    You can do this using the following logic:

     

    1. Anio+ Mes field:

    Datescombined = Table[Anio] + Table[Mes]
     
    2. Maximum Anio+ Mes:
    3. Datescombined MAX = MAX(Table[Datescombined])
     
    Is Anio+ Mes the maximum:
    Max Date Flag = IF(Table[Datescombined]=Table[Datescombined MAX],1,0)
     
    You can then filter your dataset to include any fields in the Max Date Flag field with a value of 1
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you very much S_JB.
      In the attempts I made earlier, I considered something close to what was suggested. In this case it didn't work for me because the result of "Datescombined MAX = MAX (Table [Datescombined])" was 2031 in the whole column (this is 2019 + 12, instead of 2020 + 9).

      Thankful for your support.

      • S_JB's avatar
        S_JB
        Icon for Resolver III rankResolver III
        5 years ago

        I had missed this, apologies. This could have been worked around using an IF statement that defaulted 2019 to 0 for the calculated column.

        It appears the issue has been resolved though now.

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    you can try to create a new table get the latest exchange rate.

    Table 2 = 
var MAXMONTH=MAXX(FILTER('Table','Table'[year]=YEAR(TODAY())),'Table'[month])
RETURN SUMMARIZE(FILTER('Table','Table'[year]=YEAR(TODAY())&&'Table'[month]=MAXMONTH),'Table'[Currency],'Table'[Exchange Rate])

    please see the attachment below

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you very much ryan_mayu .

      With the offered solution I get very close to what I need. I got the maximum values ​​for each type of currency. Now with this result I imagine doing two things:

      1. How can I multiply all my sales by the maximum value for USD, in this case 773,959 ...?
      2. How can I make all my sales multiply by the maximum value of a currency that I choose in a filter or slicer?

      Cheers,