Hello Experts,I need some help with Dynamic currency conversion.Here is my requestI 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 dataSales OrganizationPlantInvoice DateInvoiced Net SalesDate NetGI Net Sales Item Month/Year Net Sales JV Commercial1/1/2022 0:00300010/1/2019 0:00115195123.710/1/2019 0:00100 JV Commercial1/2/2022 0:00500010/2/2019 0:00107449259.810/2/2019 0:0020000 JV Commercial1/3/2022 0:00700010/3/2019 0:00104248420.910/3/2019 0:0039900 JV Commercial1/4/2022 0:00900010/4/2019 0:0088383536.6710/4/2019 0:0059800 JV Commercial1/5/2022 0:001100010/5/2019 0:0081430085.719/1/2018 0:0079700 JV Commercial1/6/2022 0:001300010/6/2019 0:0080904489.229/2/2018 0:0099600 JV Commercial1/7/2022 0:00150002/1/2019 0:0079026069.439/3/2018 0:00119500 JV Commercial1/8/2022 0:00170002/2/2019 0:0078458087.599/4/2018 0:00139400 JV Commercial1/9/2022 0:00190002/3/2019 0:0076410505.669/5/2018 0:00159300 JV Commercial1/10/2022 0:00210002/4/2019 0:0074064267.979/6/2018 0:00179200 JV Commercial1/11/2022 0:00230002/5/2019 0:0070108154.6411/1/2019 0:00199100 JV Commercial2/1/2022 0:00250002/6/2019 0:0067304229.1411/2/2019 0:00219000 JVRiver2/2/2022 0:00270002/7/2019 0:0062473692.1711/3/2019 0:00238900 JVRiver2/3/2022 0:00290002/8/2019 0:0061522920.4611/4/2019 0:00258800 JVRiver2/4/2022 0:00310002/1/2022 0:0059491254.8711/5/2019 0:00278700 JVRiver2/5/2022 0:00330002/2/2022 0:0058135935.038/1/2016 0:00298600IURiver3/1/2022 0:00350002/3/2022 0:0057121334.088/2/2016 0:00318500IURiver3/2/2022 0:00370002/4/2022 0:0056813023.248/3/2016 0:00338400IURiver3/3/2022 0:003900010/1/2018 0:0056641166.68/4/2016 0:00358300IURiver3/4/2022 0:004100010/2/2018 0:0054169527.838/5/2016 0:00378200IURiver3/5/2022 0:004300010/3/2018 0:0054081676.794/1/2019 0:00398100IURiver3/6/2022 0:004500010/4/2018 0:0053966513.674/2/2019 0:00418000IUCS3/7/2022 0:004700010/5/2018 0:0052486989.564/3/2019 0:00437900IUCS6/1/2022 0:004900010/6/2018 0:0051375353.614/4/2019 0:00457800IUCS6/2/2022 0:005100010/7/2018 0:0050744893.164/5/2019 0:00477700IUCS6/3/2022 0:005300010/8/2018 0:0050261684.954/6/2019 0:00497600IUCS6/4/2022 0:005500010/9/2018 0:0048337385.633/1/2022 0:00517500IUCS6/5/2022 0:0057000#############48163206.643/2/2022 0:00537400IUCS6/6/2022 0:00590002/1/2020 0:0047219778.823/3/2022 0:00557300IUCS6/7/2022 0:006100010/1/2017 0:0046234021.113/4/2022 0:00577200IUCS6/8/2022 0:00630008/1/2018 0:0045717685.873/5/2022 0:00597100IUCS6/9/2022 0:00650008/2/2018 0:0045067409.823/6/2022 0:00617000IUCS6/10/2022 0:00670008/3/2018 0:0044997749.186/1/2019 0:00636900IUCS6/11/2022 0:00690008/4/2018 0:0044794696.866/2/2019 0:00656800AACS6/12/2022 0:00710008/5/2018 0:0044206922.076/3/2019 0:00676700AACS9/1/2022 0:00730008/6/2018 0:0044091486.936/4/2019 0:00696600AACS9/2/2022 0:00750008/7/2018 0:0043499169.066/5/2019 0:00716500

Helper III

3 years ago

Solved

Dynamic currency conversion help

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

3 years ago

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
        )
    )

7 Replies

johnt75

Super User

3 years ago

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

Helper III

3 years ago

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

Super User

3 years ago

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
        )
    )

Forum Discussion

Dynamic currency conversion help

7 Replies

Related content

Function currency conversion

Dynamic currency conversion based on Account Manager location in Power BI

Currency conversion

Currency Conversion

Implementing Dynamic Currency Conversion in Power BI