Currency Conversion Normalization - Time Dependent

[Anonymous]

Let's say in my Great Britain company,  I make a total net profit of 10,000 USD (USD is the reporting currency) in January 2020. I then have to convert this 10,000 USD to GBP (Pounds) based on the January 2020 exchange rate (e.g 0.86).

So now we have a total net profit of 8600 GBP (local currency) for January 2020.  I have completed this successfully using the below DAX code:

Local Currency Net Sales =

IF (

HASONEVALUE ( 'Source Currency'[To Currency] ),

VAR AggregatedSalesInDKK =

ADDCOLUMNS (

SUMMARIZE (

'Fact PMA',

'Dim Main Calendar'[Year-Month Sort]), --Monthly granularity

"@SalesAmount", [Total Net Sales],

"@Rate", CALCULATE ( SELECTEDVALUE ( 'Fact Exchange Rates Type C'[Exchange Rate] ) )

)

VAR Result =

IF(SELECTEDVALUE('Source Currency'[To Currency]) = "DKK",

[Total Net Sales],

SUMX (

AggregatedSalesInDKK,

[@SalesAmount] * [@Rate]))

RETURN

Result

)

Now, in order to normalize the currency conversion, and enable fair variance comparison,  year-on-year, I must convert the GBP back into USD, based on the January 2021 exchange rate. E.g GBP-USD Jan 2021 = 1.15, so the normalized USD value would be 9890 USD (8600*1.15). 

Does anyone know how to create this time-dependency?

Any help is greatly appreciated!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I made a sample to have a test.

Data Table:

Rate Table:

Calendar Table:

Calendar = ADDCOLUMNS(CALENDAR(DATE(2020,01,01),DATE(2021,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]))

Measures:

GBP = 
VAR _RATE =
    CALCULATE (
        SUM ( 'Rate'[Rate USD-GBP] ),
        FILTER (
            'Rate',
            AND (
                'Rate'[Year] = MAX ( 'Table'[Year] ),
                'Rate'[Month] = MAX ( 'Table'[Month] )
            )
        )
    )
VAR _GBP =
    _RATE * SUM ( 'Table'[USD] )
RETURN
    _GBP

USD in Current Rate = 
VAR _SELECTYEAR =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR _SELECTMONTH =
    SELECTEDVALUE ( 'Calendar'[Month] )
VAR _RATE =
    CALCULATE (
        SUM ( 'Rate'[Rate USD-GBP] ),
        FILTER (
            ALL ( 'Rate' ),
            'Rate'[Year] = _SELECTYEAR
                && 'Rate'[Month] = _SELECTMONTH
        )
    )
RETURN
    ROUND ( DIVIDE ( 1, _RATE ), 2 ) * [GBP]

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.