topic Re: Dynamic currency conversion help in DAX Commands and Tips

Dynamic currency conversion help

Puja — Tue, 11 Oct 2022 02:40:55 GMT

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

Re: Dynamic currency conversion help

johnt75 — Tue, 11 Oct 2022 11:21:56 GMT

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

Re: Dynamic currency conversion help

Puja — Wed, 12 Oct 2022 02:54:23 GMT

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

Re: Dynamic currency conversion help

johnt75 — Wed, 12 Oct 2022 08:44:55 GMT

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

Re: Dynamic currency conversion help

Puja — Wed, 12 Oct 2022 22:41:07 GMT

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 🙂

Re: Dynamic currency conversion help

johnt75 — Thu, 13 Oct 2022 08:12:44 GMT

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

Re: Dynamic currency conversion help

Puja — Thu, 13 Oct 2022 10:44:52 GMT

Hi johnt75

Yes, I created the way you discribed.

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

Its not working.

Re: Dynamic currency conversion help

johnt75 — Thu, 13 Oct 2022 13:55:45 GMT

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