Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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]))