Using the latest figure from one table and use it in another

[ThomasSan]

Hi,

I am attempting to use PowerBI as a simulator tool. My goal is to calculate non-Euro sales figures to Euro values by using the latest exchange rate depending on the settings of my date slicer. 

I have an exchange rate table (named "ER") that has the strucutre

Date	Year	Month	Exchange Rate to Euro	Currency
1.1.10	10	1	55	AAA
1.1.10	10	1	56	BBB
1.2.10	10	2	57	AAA
1.2.10	10	2	58	BBB
1.1.20	20	1	59	AAA
1.1.20	20	1	60	BBB
1.2.20	20	2	61	AAA
1.2.20	20	2	62	BBB

and I have a sales table (named "Sales") that has the structure

Date	Year	Month	Sales in Local Currency	Country	Currency
1.2.20	20	2	856	bb	BBB
1.2.10	10	2	566	bb	BBB
1.1.10	10	1	545	aa	AAA
1.1.20	20	1	443	aa	AAA
1.2.10	10	2	324	aa	AAA
1.1.20	20	1	231	bb	BBB
1.1.10	10	1	212	bb	BBB
1.2.20	20	2	112	aa	AAA

and I have a simple date table (named "Date") that is connected to the sales table with a 1-to-many cardinality.

What is the dax command that I can use in order to map the correct exchange rate figure to each currency in my sales table (depending on the settings of the date slicer)?

My current formula looks as follows:

SalesEURO latest ER = 

var saleslocal=
sum(Sales[Sales in Local Currency])

var ERlatest=
CALCULATE(
    sum(ER[Exchange Rate]),
    filter(
        'ER',
        ER[Date].[Date] = max('Date'[Date])
        )
    )

return saleslocal*ERlatest

I feel like I am overthinking this...

[ThomasSan]

Hi @v-binbinyu-msft 

that looks really good (and I learnt something new today about DAX). Thanks a lot!

[v-binbinyu-msft · Accepted solution]

Hi @ThomasSan ,

Please try to create a measure with below dax formula:

Measure =
VAR _date =
    MAX ( 'Date'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= _date )
VAR latest_date =
    MAXX ( tmp, [Date] )
VAR cur_currency =
    SELECTEDVALUE ( 'Table 2'[Currency] )
VAR cur_currency_sale =
    SELECTEDVALUE ( 'Table 2'[Sales in Local Currency] )
VAR _rate =
    CALCULATE (
        MAX ( 'Table'[Exchange Rate to Euro] ),
        'Table'[Date] = latest_date,
        'Table'[Currency] = cur_currency,
        ALL ( 'Table' )
    )
RETURN
    cur_currency_sale * _rate

For more details, please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi @ThomasSan please check dedicated video for your / similar topic

https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/ 

Hope this help, kudos appreciated.