Hi all,
I've been going around in circles with SUMMARIZE, GROUP BY with no effect. Here's my problem:
I have a currency exchange rate table with the following columns: Currency Code, Currency Rate, Valid From, Qtr
Above data is entered by my finance dept. on a monthly basis into our source system. I'm retrieving all of it but I need to find out what the currency rate was at the end of each Qtr, so the ideal output (probably as a new table) would look like:
Currency Code | Currency Rate | Valid From |
AUD | 1.777254 | 01/12/2022 |
BRL | 6.332406 | 01/12/2022 |
EUR | 1.169222 | 01/12/2022 |
USD | 1.231575 | 01/12/2022 |
ZAR | 20.184808 | 01/12/2022 |
AUD | 1.790811 | 01/03/2023 |
BRL | 6.268214 | 01/03/2023 |
EUR | 1.13509 | 01/03/2023 |
USD | 1.2234 | 01/03/2023 |
ZAR | 21.812421 | 01/03/2023 |
CNY | 8.368982 | 01/03/2023 |
I usually try to solve things as much as I can on my own before turning to the community. I could really appreciate someone shining some light on this, I must be missing something.
Solved! Go to Solution.
I managed to fix my own problem by using the following
Quarterly Rates = SUMMARIZE(
'IFS Currency Exchange Rates',
'IFS Currency Exchange Rates'[CURRENCY_CODE],
'IFS Currency Exchange Rates'[Qtr],
"Max Date",
MAX('IFS Currency Exchange Rates'[VALID_FROM]),
"Rates",
LOOKUPVALUE('IFS Currency Exchange Rates'[CURRENCY_RATE],
'IFS Currency Exchange Rates'[VALID_FROM],
MAX('IFS Currency Exchange Rates'[VALID_FROM]),
'IFS Currency Exchange Rates'[CURRENCY_CODE],
'IFS Currency Exchange Rates'[CURRENCY_CODE]))
I managed to fix my own problem by using the following
Quarterly Rates = SUMMARIZE(
'IFS Currency Exchange Rates',
'IFS Currency Exchange Rates'[CURRENCY_CODE],
'IFS Currency Exchange Rates'[Qtr],
"Max Date",
MAX('IFS Currency Exchange Rates'[VALID_FROM]),
"Rates",
LOOKUPVALUE('IFS Currency Exchange Rates'[CURRENCY_RATE],
'IFS Currency Exchange Rates'[VALID_FROM],
MAX('IFS Currency Exchange Rates'[VALID_FROM]),
'IFS Currency Exchange Rates'[CURRENCY_CODE],
'IFS Currency Exchange Rates'[CURRENCY_CODE]))