Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
Thanks a lot for looking into my problem. That formula works perfectly for me. What a great community this is.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |