cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Apply last exchange rate in a dynamically selected period across all transactions in the period

Hi, I am new to DAX and am struggling with how to fulfill a requirement on a Power BI report.

The base data is from a general ledger account with around 1.5million transaction rows. I also have an exchange rate table with exchange rates per currency per month. The requirement from the finance team is for them to be able to dynamicaly select 1 or multiple periods (year and month) from the data and see the transaction amounts in GBP using the last exchange rate in the selected period. So if they filter the report to show periods 1,2 and 3 for 2019 they expect all transactions from these three periods to be converted to GBP using the rate as at period 3. Here is some example data.

Exchange rate table.

 CURR Date From Date To Year Month Rate EUR 01/01/2019 31/01/2019 2019 1 1.16 EUR 01/02/2019 28/02/2019 2019 2 1.15 EUR 01/03/2019 31/03/2019 2019 3 1.17 EUR 01/04/2019 30/04/2019 2019 4 1.18 USD 01/01/2019 31/01/2019 2019 1 1.25 USD 01/02/2019 28/02/2019 2019 2 1.26 USD 01/03/2019 31/03/2019 2019 3 1.31 USD 01/04/2019 30/04/2019 2019 4 1.27

General ledger data

 Tran Date CURR Amt Year Month a1 02/01/2019 USD 100 2019 1 a2 17/01/2019 USD 80 2019 1 a3 10/02/2019 USD 79 2019 2 a4 12/02/2019 USD 76 2019 2 a5 15/02/2019 USD 123 2019 2 a6 17/02/2019 USD 67 2019 2 a7 05/03/2019 USD 104 2019 3 a8 11/03/2019 USD 110 2019 3

If the report filter was set to select periods 1,2 and 3 of 2019 they would want the amount converting using rate 1.31 which is the period 3 rate.

 Tran Amt a1 131 a2 104.8 a3 103.49 a4 99.56 a5 161.13 a6 87.77 a7 136.24 a8 144.1

If the filter was changed to period 1 and 2 only then the transactions for periods 1 and 2 should be converted to GBP at the period 2 rate (1.26). I hope the requirement is clear.

I have struggled to do this using DAX formulas and I suspect I haven't quite got my head around the filter contexts etc. needed in DAX. I am trying to learn.

I have tried various snippets of code taken from this forum and others to try and get the rate for the last filtered period and then use this against all selected transaction rows but I get a memory exception no matter what I try and the laptop I am running on has 24GB RAM so I hope that is not the issue. I suspect it is a poor formula construction that is the issue. I have tried various functions such as MAX, MAXX, LASTNONBLANK, LASTDATED, LOOKUPVALUES, RELATED, LATER and have tried to use the FILTER and CALCULATE commands as well as trying to add and remove contexts with ALL(), ALLSELECTED, ALLEXCEPT and KEEPFILTERS.

I need a little guidance on how to create something that performs. Ultimately they want to use the Analyze in Excel functionality on this dataset to get the data into a pivot table that they will apply the period filters to in Excel and want to see all the sum of the filtered transaction amounts at the latest exchange rate for the period and with the ability to drill down to look at individual transaction amounts. Any help would be appreciated.

1 ACCEPTED SOLUTION
Community Support

Hi @Morton041740 ,

You can use following measure formula to get correspond last currency rate to calculate with current amt:

Result =
VAR _currDate =
MAX ( calendar[Date] )
VAR _currency =
SELECTEDVALUE ( 'General ledger'[CURR] )
VAR _rate =
CALCULATE (
MAX ( 'Exchange Rate'[Rate] ),
FILTER (
ALL ( 'Exchange Rate' ),
[CURR] = _currency
&& _currDate IN CALENDAR ( [Date From], [Date To] )
)
)
RETURN
CALCULATE (
SUM ( 'General ledger'[Amt] ) * _rate,
VALUES ( 'General ledger'[Tran] )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Community Support

Hi @Morton041740 ,

You can use following measure formula to get correspond last currency rate to calculate with current amt:

Result =
VAR _currDate =
MAX ( calendar[Date] )
VAR _currency =
SELECTEDVALUE ( 'General ledger'[CURR] )
VAR _rate =
CALCULATE (
MAX ( 'Exchange Rate'[Rate] ),
FILTER (
ALL ( 'Exchange Rate' ),
[CURR] = _currency
&& _currDate IN CALENDAR ( [Date From], [Date To] )
)
)
RETURN
CALCULATE (
SUM ( 'General ledger'[Amt] ) * _rate,
VALUES ( 'General ledger'[Tran] )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

Thanks a lot for looking into my problem. That formula works perfectly for me. What a great community this is.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors