Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Please see the attachment. Hope this is helpful.
Proud to be a Super User!
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
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,
Please see the attachment. Hope this is helpful.
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!
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |