default report currency and Dynamic currency conversion

[Mous007]

hello all,

I have been tasked to develop a payables report page and I have several reports from different countries. Each country has invoices that can be paid in local currency but in foreign currency also as you can see in the extract below. 

The area where i need serious help is if anybody can help me understand

1. How i can set up the default currency for EUR (meaning that i want the report to dynamically convert all invoices amount to EUR)
2. how i can set up a filter on the page where my users can switch the full report numbers to another currency (other than EUR)

I have been provided with a currency table and i think it should be used as a sort of converter but i am not really sure how to use it. The currency table is also attached below.

IF anybody can help me start this currency conversion issue i would really appreciate it. or at least guide me on where or what should i be looking for to solve the issue. any other infos or details needed please let me know.

Invoice Num	Invoice Amount	Currency Code
Invoice 1	254.57	AUD
Invoice 2	608.08	AUD
Invoice 3	6114.84	AUD
Invoice 4	898.03	AUD
Invoice 5	160	AUD
Invoice 6	3953.4	AUD
Invoice 7	4135.42	AUD
Invoice 8	11477.8	AUD
Invoice 9	942.5	AUD
Invoice 10	3605.47	EUR
Invoice 11	18685.84	EUR
Invoice 12	6988.8	EUR
Invoice 13	7474.34	EUR
Invoice 14	32732.2	EUR
Invoice 15	18919.69	GBP
Invoice 16	964.51	GBP
Invoice 17	6872.54	GBP
Invoice 18	126.86	MYR
Invoice 19	27790.35	MYR
Invoice 20	342033.86	SGD
Invoice 21	3228.91	SGD
Invoice 22	3228.91	SGD
Invoice 23	21526.08	SGD

CURRENCY_ID	VALUE
AED	0.23364486
AFN	0.011764706
ALL	0.007692308
AMD	0.001785714
ANG	0.4784689
AOA	0.003448276
ARS	0.023809524
AUD	0.632911392
AWG	0.4784689
AZN	0.505050505
BAM	0.511247444
BBD	0.429184549
BDT	0.010204082
BGN	0.511247444
BHD	2.272727273
BIF	0.000487805
BMD	0.854700855
BND	0.628930818
BOB	0.12345679
BRL	0.222222222
BSD	0.854700855
BTN	0.0125
BWP	0.083333333
BYN	0.431034483
BZD	0.429184549
CAD	0.649350649
CDF	0.000531915
CHF	0.864304235
CLP	0.001315789
CNY	0.12987013
COP	0.000290698
CRC	0.001515152
CUC	0.854700855
CVE	0.009090909
CZK	0.038461538
DJF	0.004830918
DKK	0.133333333
DOP	0.017241379
DZD	0.007142857
EGP	0.04784689
ERN	0.057142857
ETB	0.030864198
EUR	1
FJD	0.409836066
GBP	1.136363636
GEL	0.34965035
GHS	0.178571429
GMD	0.017857143
GNF	9.5057E-05
GTQ	0.114942529
GYD	0.004291845
HKD	0.10989011
HNL	0.035842294
HRK	0.135135135
HTG	0.012987013
HUF	0.003030303
IDR	5.98802E-05
ILS	0.234741784
INR	0.0125
IQD	0.000718907
IRR	1.08696E-05
ISK	0.008130081
JMD	0.006622517
JOD	1.207729469
JPY	0.0078125
KES	0.008474576
KGS	0.0125
KHR	0.000210526
KMF	0.002040816
KPW	0.006666667
KRW	0.000769231
KWD	2.83286119
KYD	1.020408163
KZT	0.002512563
LAK	0.000102354
LBP	0.000569152
LKR	0.005405405
LRD	0.005649718
LSL	0.0625
LYD	0.625
MAD	0.090909091
MDL	0.05
MGA	0.000253807
MKD	0.016129032
MMK	0.000632911
MNT	0.000348189
MOP	0.106382979
MRU	0.025
MUR	0.024630542
MVR	0.055555556
MWK	0.001182033
MXN	0.043478261
MYR	0.212765957
MZN	0.014492754
NAD	0.0625
NGN	0.002380952
NIO	0.027027027
NOK	0.105263158
NPR	0.0078125
NZD	0.581395349
OMR	2.227171492
PAB	0.854700855
PEN	0.261780105
PGK	0.261096606
PHP	0.016129032
PKR	0.007042254
PLN	0.228832952
PYG	0.000150602
QAR	0.235849057
RON	0.214592275
RSD	0.008474576
RUB	0.01369863
RWF	0.000980392
SAR	0.228832952
SBD	0.10989011
SCR	0.063291139
SDG	0.047619048
SEK	0.096153846
SGD	0.628930818
SLL	0.000104603
SOS	0.001492537
SRD	0.114942529
SSP	0.006134969
STN	0.04
SYP	0.001666667
SZL	0.0625
THB	0.025641026
TJS	0.090909091
TMT	0.25
TND	0.328947368
TOP	0.383141762
TRY	0.161290323
TTD	0.126582278
TWD	0.028169014
TZS	0.000377358
UAH	0.032258065
UGX	0.000221239
USD	0.854700855
UYU	0.027027027
UZS	0.000108932
VEF	8.92857E-06
VES	0.01369863
VND	3.73134E-05
VUV	0.007751938
WST	0.333333333
XAF	0.00152439
XCD	0.316455696
XDR	1.213592233
XOF	0.00152439
XPF	0.008403361
YER	0.003424658
ZAR	0.0625
ZMW	0.086206897

Thanks in advance.

Mous

[jdbuchanan71]

Hello @Mous007 

Your currency table is the conversion rates from any currency to EUR so we can join it to the invoice table on the currency ID and do a calculation like so to go from invoice currency to EUR.

Invoice Amount EUR = SUMX(Invoices,Invoices[Invoice Amount] * RELATED(Currencies[VALUE]))

Now that we have the EUR amount we can use a copy of the currency table to let the user select another conversion rate and take all invoices from EUR to the selected rate.

Invoice Amount Selected Currency = 
VAR _ConversionRate = SELECTEDVALUE('Currencies Disconnected'[VALUE],1)
RETURN DIVIDE([Invoice Amount EUR],_ConversionRate)

In this measure, if no selection is made or they select multiple "secondary conversion" currencies it will leave the amount in EUR otherwise it will convert to the selected currency.  In my screen shot below I show invoice amount in original currency, EUR and AUD (I have AUD selected from the disconnected table).  You can see the first and third columns match for invoices that were in AUD to begin with.

My sample file is attached for you to look at.

[Mous007]

hi @jdbuchanan71 , thanks a lot for your support.

Your solution works perfectly for my business issue. One more question, so from now on whenever i am trying to use my payables/invoice amount in any type of calculation, i should always refer to the measure in selected currency ? or do you think i do do it in some other way ?

Thanks in advance 

I will mark ur post as a solution but i want to wait for @Greg_Deckler  repose as i want to try out the solution he provided two.

Many thanks to you both. different approaches but i am learning from  both.

[jdbuchanan71]

You could use the [Invoice Amount Selected Currency] and have the report default to EUR.  That would probably be easiest for your users.

[Mous007]

Hi @jdbuchanan71 , thank you for the clarification.

I just had another question that came to my mind while trying to build my main measures.

So based on my understanding in your scenario using this technique, if for example i had different kpi's or metrics i want to use in my model such as: Invoice amount(which we already calculated), Remaing amount, Approved amount , Paid amount etc.... , it means that for each one of these i will have to create two separate measure for each ?

Is there any method i can use to optimiye my calculation ? instead of having several measures on my measure table ?

I was thinking of creating a measure with multiple varibles such as the one used by Greg above where i can basically group both measures into one ?

Please let me know if that is the best approach or if you have any other suggestions.

Many thanks again.

[jdbuchanan71]

Hello @Mous007 

You can do it all in 1 step, I just had two because of your request to always have EUR available.

Invoice Amount Converted = 
VAR _ConversionRate =
    SELECTEDVALUE ( 'Currencies Disconnected'[VALUE], 1 )
RETURN
    SUMX (
        Invoices,
        DIVIDE (
            Invoices[Invoice Amount] * RELATED ( Currencies[VALUE] ),
            _ConversionRate
        )
    )

You would just change the column feeding into the measure for each of your calcs:

[romgut]

It's working! Thank you so much!

First I used the original currencies table and it didn't work because the lines were filtered, but with the disconnected copy of the currencies table it's just fine!

[Mous007]

Thank you @jdbuchanan71 , i am still getting the same correct results with in EUR and of course whenever i change the currency from the disconnected slicer everything is working as expected.

Many thanks again.

[Greg_Deckler]

I have nearly this exact scenario in my book, DAX Cookbook. Crafting Currency Exchange rates, Chapter 5, page 184. Mine might be a little more complicated than yours because my scenario has the currency exchange rate change over time.

It goes something like the following:

Reporting Amount = 
    VAR __ReportingCurrency = SELECTEDVALUE('R08_Currencies'[Currency])
    VAR __SalesCurrency = MAX('R08_Sales'[Currency])
    VAR __SalesDate = MAX('R08_Sales'[Date])
    VAR __SalesAmount = SUM('R08_Sales'[Amount])
    VAR __ExchangeRateDate = 
        MAXX(
            FILTER(
                'R08_Rates',
                    'R08_Rates'[Date] <= __SalesDate &&
                        'R08_Rates'[From] = __SalesCurrency &&
                            'R08_Rates'[To] = __ReportingCurrency
            ),
            'R08_Rates'[Date]
        )
    VAR __ExchangeRate = 
        IF(__SalesCurrency = __ReportingCurrency,
            1,
            MAXX(
                FILTER(
                    'R08_Rates',
                    'R08_Rates'[Date] = __ExchangeRateDate &&
                        'R08_Rates'[From] = __SalesCurrency &&
                            'R08_Rates'[To] = __ReportingCurrency
                ),
                'R08_Rates'[Rate]
            )
        )
RETURN
    __ExchangeRate * __SalesAmount

[Anonymous]

Hi Greg,

I have used the Code below that is your's and I have had success with it in certain situations but I haven't quite got what I need and was hoping for a couple of pointers.

I have a Sales table which has a an amount column and in that column it has amounts for USD, EUR and GBP.  I also have a CurrencyID Column that tells me what the Currency is.  I need to convert that column into a single Currency which will be decided by the User via a Slicer.  I have used your measure below to do that and it works brilliantly when I select Sales Records for a Single Currency on a Single Date.  I think the reason it isn't working is the measure selects the Rate by MAXX Date and Currency and that isn't quite what I need but I cannot figure out how the iteration should work correctly.  Any help greatfully received, apologies if I shouldn't have contacted you.

Thanks

Chris

[Anonymous]

Hi Greg,

From what you are saying I think you have the solution I require.  We capture exchange rates for a number of countries on a daily basis and keep the in a table. We also have a sales table has an Amount column that can be in a number of Currencies, we know which currency from the CurrencyID Column in the Sales Table.

I would like to have a Slicer on a report where the user can pick the Reporting Currency.

Do you have a picture of your model that goes with the Dax Code that I can have a look at please.  

Thanks I hope you can help.

Chris

[Mous007]

hI @Greg_Deckler , thank you for taking the time to go thru my post.

I have tried to use your measure but got stuck in the following line:

                        'R08_Rates'[From] = __SalesCurrency &&
                            'R08_Rates'[To] = __ReportingCurrency

I might be wrong but i think i am supposed to have the currency conversion rates on mz main fact invoices table ? Because now i have my invoices table and currency table on two distinct tables.

Maybe you can further help me with this ?

thanks in advance