Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]))