Dynamic currency conversion help

[Puja]

Hello Experts,
I need some help with Dynamic currency conversion.

Here is my request
I have a Fact table with three dates with three amounts(below screenshot). When the user selects a currency code, these measures should be filtered by those dates with conversion rates.

I appreciate any help you can provide.

This is Exchange reate table .

Sample data

Sales Organization	Plant	Invoice Date	Invoiced Net Sales	Date Net	GI Net Sales	Item Month/Year	Net Sales
JV	Commercial	1/1/2022 0:00	3000	10/1/2019 0:00	115195123.7	10/1/2019 0:00	100
JV	Commercial	1/2/2022 0:00	5000	10/2/2019 0:00	107449259.8	10/2/2019 0:00	20000
JV	Commercial	1/3/2022 0:00	7000	10/3/2019 0:00	104248420.9	10/3/2019 0:00	39900
JV	Commercial	1/4/2022 0:00	9000	10/4/2019 0:00	88383536.67	10/4/2019 0:00	59800
JV	Commercial	1/5/2022 0:00	11000	10/5/2019 0:00	81430085.71	9/1/2018 0:00	79700
JV	Commercial	1/6/2022 0:00	13000	10/6/2019 0:00	80904489.22	9/2/2018 0:00	99600
JV	Commercial	1/7/2022 0:00	15000	2/1/2019 0:00	79026069.43	9/3/2018 0:00	119500
JV	Commercial	1/8/2022 0:00	17000	2/2/2019 0:00	78458087.59	9/4/2018 0:00	139400
JV	Commercial	1/9/2022 0:00	19000	2/3/2019 0:00	76410505.66	9/5/2018 0:00	159300
JV	Commercial	1/10/2022 0:00	21000	2/4/2019 0:00	74064267.97	9/6/2018 0:00	179200
JV	Commercial	1/11/2022 0:00	23000	2/5/2019 0:00	70108154.64	11/1/2019 0:00	199100
JV	Commercial	2/1/2022 0:00	25000	2/6/2019 0:00	67304229.14	11/2/2019 0:00	219000
JV	River	2/2/2022 0:00	27000	2/7/2019 0:00	62473692.17	11/3/2019 0:00	238900
JV	River	2/3/2022 0:00	29000	2/8/2019 0:00	61522920.46	11/4/2019 0:00	258800
JV	River	2/4/2022 0:00	31000	2/1/2022 0:00	59491254.87	11/5/2019 0:00	278700
JV	River	2/5/2022 0:00	33000	2/2/2022 0:00	58135935.03	8/1/2016 0:00	298600
IU	River	3/1/2022 0:00	35000	2/3/2022 0:00	57121334.08	8/2/2016 0:00	318500
IU	River	3/2/2022 0:00	37000	2/4/2022 0:00	56813023.24	8/3/2016 0:00	338400
IU	River	3/3/2022 0:00	39000	10/1/2018 0:00	56641166.6	8/4/2016 0:00	358300
IU	River	3/4/2022 0:00	41000	10/2/2018 0:00	54169527.83	8/5/2016 0:00	378200
IU	River	3/5/2022 0:00	43000	10/3/2018 0:00	54081676.79	4/1/2019 0:00	398100
IU	River	3/6/2022 0:00	45000	10/4/2018 0:00	53966513.67	4/2/2019 0:00	418000
IU	CS	3/7/2022 0:00	47000	10/5/2018 0:00	52486989.56	4/3/2019 0:00	437900
IU	CS	6/1/2022 0:00	49000	10/6/2018 0:00	51375353.61	4/4/2019 0:00	457800
IU	CS	6/2/2022 0:00	51000	10/7/2018 0:00	50744893.16	4/5/2019 0:00	477700
IU	CS	6/3/2022 0:00	53000	10/8/2018 0:00	50261684.95	4/6/2019 0:00	497600
IU	CS	6/4/2022 0:00	55000	10/9/2018 0:00	48337385.63	3/1/2022 0:00	517500
IU	CS	6/5/2022 0:00	57000	#############	48163206.64	3/2/2022 0:00	537400
IU	CS	6/6/2022 0:00	59000	2/1/2020 0:00	47219778.82	3/3/2022 0:00	557300
IU	CS	6/7/2022 0:00	61000	10/1/2017 0:00	46234021.11	3/4/2022 0:00	577200
IU	CS	6/8/2022 0:00	63000	8/1/2018 0:00	45717685.87	3/5/2022 0:00	597100
IU	CS	6/9/2022 0:00	65000	8/2/2018 0:00	45067409.82	3/6/2022 0:00	617000
IU	CS	6/10/2022 0:00	67000	8/3/2018 0:00	44997749.18	6/1/2019 0:00	636900
IU	CS	6/11/2022 0:00	69000	8/4/2018 0:00	44794696.86	6/2/2019 0:00	656800
AA	CS	6/12/2022 0:00	71000	8/5/2018 0:00	44206922.07	6/3/2019 0:00	676700
AA	CS	9/1/2022 0:00	73000	8/6/2018 0:00	44091486.93	6/4/2019 0:00	696600
AA	CS	9/2/2022 0:00	75000	8/7/2018 0:00	43499169.06	6/5/2019 0:00	716500

[johnt75]

Try

Invoiced net sales converted =
VAR SelectedCurrency =
    SELECTEDVALUE ( 'Exch rate'[Currency] )
RETURN
    IF (
        NOT ISBLANK ( SelectedCurrency ),
        SUMX (
            'Table',
            VAR ReferenceDate = 'Table'[Invoice date]
            VAR ReferenceAmount = 'Table'[Invoiced net sales]
            VAR ExchRate =
                CALCULATE (
                    MAX ( 'Exch rate'[_EXCH_RATE] ),
                    'Exch rate'[Currency] = SelectedCurrency,
                    'Exch rate'[_FROM_DT] <= ReferenceDate
                        && 'Exch rate'[_TO_DT] >= ReferenceDate
                )
            RETURN
                ExchRate * ReferenceAmount
        )
    )

[Puja]

Hi @johnt75 , 

Thanks for the solution.

I tested your solution and it looks like its only reading data on 1st of each month.

In exchange table , I have ave rate by month.

Ex:  date             Exch  AVE rate

     6/1/2020       0.12

     7/1/2020       0.09

     8/1/2020       1.19

Example : 6/1/2020  Converting correctly.

                6/2/2020 I see blank althought there is data.

Could you please help.

Thank you SO MUCH

[johnt75 · Accepted solution]

try

Invoiced net sales converted =
VAR SelectedCurrency =
    SELECTEDVALUE ( 'Exch rate'[Currency] )
RETURN
    IF (
        NOT ISBLANK ( SelectedCurrency ),
        SUMX (
            'Table',
            VAR ReferenceDate = 'Table'[Invoice date]
            VAR ReferenceAmount = 'Table'[Invoiced net sales]
            VAR ExchRate =
                CALCULATE (
                    MAX ( 'Exch rate'[_EXCH_RATE] ),
                    'Exch rate'[Currency] = SelectedCurrency,
                    'Exch rate'[Date] = DATE( YEAR( ReferenceDate), MONTH(ReferenceDate),1)
                )
            RETURN
                ExchRate * ReferenceAmount
        )
    )

[Puja]

@johnt75 ,

This is working GREAT. Thank you SO MUCH.
I want to ask for some more help. How can I use this same measure with the USERELATIONSHIP function? I have two other dates looking at different amounts in the same fact table. I did try, but it's not working for me.

THANKS AGAIN 🙂

[johnt75]

You'd need to change the columns in the ReferenceDate and ReferenceAmount columns. If you need to be able to chart it against the different dates you might wrap the whole thing inside CALCULATE( ..., USERELATIONSHIP('Table2'[Date],'Date'[Date]))

[Puja]

Hi @johnt75 

Yes, I created the way you discribed.

VAR ReferenceDate = CALCULATE(VALUES('FACT'[ DATE]), USERELATIONSHIP('DIMDATE'[CalendarDate], 'FACT'[DATE]))

Its not working.

[johnt75]

I meant something like

Using different date =
CALCULATE (
    VAR SelectedCurrency =
        SELECTEDVALUE ( 'Exch rate'[Currency] )
    RETURN
        IF (
            NOT ISBLANK ( SelectedCurrency ),
            SUMX (
                'Table',
                VAR ReferenceDate = 'Table'[Date 2]
                VAR ReferenceAmount = 'Table'[Different amount]
                VAR ExchRate =
                    CALCULATE (
                        MAX ( 'Exch rate'[_EXCH_RATE] ),
                        'Exch rate'[Currency] = SelectedCurrency,
                        'Exch rate'[Date] = DATE ( YEAR ( ReferenceDate ), MONTH ( ReferenceDate ), 1 )
                    )
                RETURN
                    ExchRate * ReferenceAmount
            )
        ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[Date 2] )
)