Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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]))
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
67 | |
64 | |
42 | |
42 |
User | Count |
---|---|
46 | |
38 | |
28 | |
26 | |
25 |