Get currency exchange rate per each quarter within dataset

[kasiaw29]

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. 

[kasiaw29 · Accepted 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]))