Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RichardFig1
Helper I
Helper I

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:

 
 

table.PNG

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

1 ACCEPTED SOLUTION

@RichardFig1 

Please see the attachment. Hope this is helpful.

 

 





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

Proud to be a Super User!




View solution in original post

7 REPLIES 7
ryan_mayu
Super User
Super User

@RichardFig1 

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

1.PNG

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

2.PNG

please see the attachment below





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

Proud to be a Super User!




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,

@RichardFig1 

Please see the attachment. Hope this is helpful.

 

 





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

Proud to be a Super User!




Excellent! Thank you very much @ryan_mayu for your help!
With this I solve very well how to obtain the exchange rate for my project.
Have a nice week!

S_JB
Resolver III
Resolver III

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

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.