Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Morton041740
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
v-shex-msft
Community Support
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] )
    )

68.gif

Regards,

Xiaoxin Sheng

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

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
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] )
    )

68.gif

Regards,

Xiaoxin Sheng

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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