cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Morton041740 Frequent Visitor
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.

CURRDate FromDate ToYearMonthRate
EUR01/01/201931/01/2019201911.16
EUR01/02/201928/02/2019201921.15
EUR01/03/201931/03/2019201931.17
EUR01/04/201930/04/2019201941.18
USD01/01/201931/01/2019201911.25
USD01/02/201928/02/2019201921.26
USD01/03/201931/03/2019201931.31
USD01/04/201930/04/2019201941.27

 

General ledger data

TranDateCURRAmtYearMonth
a102/01/2019USD10020191
a217/01/2019USD8020191
a310/02/2019USD7920192
a412/02/2019USD7620192
a515/02/2019USD12320192
a617/02/2019USD6720192
a705/03/2019USD10420193
a811/03/2019USD11020193

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.

TranAmt
a1131
a2104.8
a3103.49
a499.56
a5161.13
a687.77
a7136.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

Accepted Solutions
Community Support Team
Community Support Team

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

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] )
    )

68.gif

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

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

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] )
    )

68.gif

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Morton041740 Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 335 members 3,052 guests
Please welcome our newest community members: