Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Currency Conversion Normalization - Time Dependent

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!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.