Get corresponding period's Exchange Rate from dimension when there is no value in Fact Table

[Ale]

First of all, I'm attaching a piece of my data. Keep in mind I selected only few records with the same Currency to make it feasible to share it here, but my real data has more than 3 million rows and 115 currencies, so I need a solution that would work for the entire dataset.

What I'm trying to achieve is the YTD value converted to EUR (Value (EUR) YTD). Here I added the Year-Month to the table just to make it easier to understand the problem, but at the end I will have only 1 month selected and I should get the correct value displayed for that month.

Let's say I select 2022-05 (May'22). In that case, my result should be the Value (LC) YTD (31,474,609.00) mutiplied by the Exchange Rate of May'22. And here is the problem: I can see YTD LC values for all months because I use the TOTALYTD() function, but I can't get the respective Exchange Rate for May'22, because there is no real data in my Fact table for that period. So the SELECTEDVALUE() in my Dax returns blank. Any idea what I can do to be able to get the respective Exchange Rate for all the months from my Exchange Rate dimension, including when there is no data in my Fact table?

Thanks in advance!

Fact table:

DocumentKey	YearPeriod	DocumentDate	PostingId	AccountId	Currency	FK_ExchangeRate	FK_Date	Value (LC)
36	2022001	2021-11-04	267	293	KRW	KRW_202201	2022-01-01	3192
43	2022001	2021-12-22	267	293	KRW	KRW_202201	2022-01-01	2100
31	2022001	2022-01-31	268	293	KRW	KRW_202201	2022-01-01	-9333,33
93	2022001	2022-01-31	268	293	KRW	KRW_202201	2022-01-01	-4928
84	2022002	2022-01-31	267	293	KRW	KRW_202202	2022-02-01	4928
75	2022002	2022-01-31	267	293	KRW	KRW_202202	2022-02-01	9333,33
84	2022002	2022-02-08	268	293	KRW	KRW_202202	2022-02-01	-5376
86	2022002	2022-02-28	268	293	KRW	KRW_202202	2022-02-01	-1120,84
42	2022002	2022-02-28	268	293	KRW	KRW_202202	2022-02-01	-15866,7
74	2022003	2022-02-28	267	293	KRW	KRW_202203	2022-03-01	15866,67
45	2022003	2022-02-28	267	293	KRW	KRW_202203	2022-03-01	1120,84
30	2022003	2022-03-21	268	293	KRW	KRW_202203	2022-03-01	-2353,76
38	2022003	2022-03-21	268	293	KRW	KRW_202203	2022-03-01	-21000

Exchange Rate table dimension:

PK_ExchangeRate	TimeId	CurrencyCode	Exchange Rate
KRW_202201	202201	KRW	0,000738764965826801
KRW_202202	202202	KRW	0,000737456627871163
KRW_202203	202203	KRW	0,000739485324721465
KRW_202204	202204	KRW	0,000741332021669876
KRW_202205	202205	KRW	0,000742348926963949
KRW_202206	202206	KRW	0,000741929861957272
KRW_202207	202207	KRW	0,000743168871622698
KRW_202208	202208	KRW	0,000743740317058877
KRW_202209	202209	KRW	0,000743740317058877
KRW_202210	202210	KRW	0,000743740317058877
KRW_202211	202211	KRW	0,000743740317058877
KRW_202212	202212	KRW	0,000743740317058877

Date table:

I have also a regular Date table created with a DAX Calendar function which has 1 row for each day from 2021 to 2023. This is linked to my fact table through the FK_Date field.

[PaulDBrown]

Sorry, I'm not sure what you need. Here is what I'm getting from the sample data you posted

[Ale]

Thanks @PaulDBrown . What's the formula you used for that? Can you share the file?

The result looks good but I can't replicate it. In my case, I use a SUMX(SUMMARIZE()) function because I need to have the conversion calculated row by row, as I have different currencies (in the example I have only 1, but as I mentioned in my post, I have in total 115 currencies.

If I simply use a formula Value LC * Exchange Rate, this will not work as the Exchange Rate must be applied on a row level for each currency and period. I can't aggregate it in any way.

The DAX I'm currently using is the following:

SUMX(
   SUMMARIZE('Fact Table', 'FX Rates'[CurrencyCode], 'Date'[Year], 'Date'[Month Number]),
             CALCULATE([Total LC YTD] * SELECTEDVALUE('Fact Table'[ExchangeRate])
   )
)

[PaulDBrown]

These are the measures shown in the table. First, the model:

YTD LC = 
CALCULATE([SUM LC Value], DATESYTD('Date Table'[Date]))

Exchange rate by period =
CALCULATE (
    AVERAGE ( 'Exchange rate dimension'[Exchange Rate] ),
    TREATAS (
        VALUES ( 'Date Table'[YearMonth] ),
        'Exchange rate dimension'[TimeId]
    )
)

Converted = [YTD LC] * [Exchange rate by period]

File attached

[Ale]

Hi @PaulDBrown , thank you so much for your support.

The solution you presented works perfectly for this dataset I provided. However, when I tried to replicate in my real data, it fails. The reason is because when I have multiple currencies, the Exchange rate by period measure is averaging all the currencies together, which does not make sense.

What I'm trying to do now is to find a way to calculate each currency individually. Imagine that in the end I will have a single value that combines all the EUR values together, but each of them was calculated separated using their respective Exchange Rates.

[PaulDBrown]

Any chance you can provide more complete sample data?

[Ale]

Hi @PaulDBrown , I sent you a PM, please let me know if you received it. The forum is blocking me from posting additional sample data I don't know why.

[Ale]

Trying to reply for the 10th time. It's literally impossible to post the data as a table (even though it worked when I created the post).

Basically, when I add a second currency, the function you created is summing up the YTD of both currencies and then multiplying by the average of the Exchange Rates, which gives the wrong result (see imagem below):

What I need instead is to calculate the EUR value for each currency individually, and after that sum them up. As you can see, the sum for the period of May should be: 39942,5244804921

Fact table:

DocumentKey YearPeriod DocumentDate PostingId AccountId Currency FK_ExchangeRate FK_Date Value (LC)
36 2022001 04.11.2021 267 293 KRW KRW_202201 01.01.2022 4288771
43 2022001 31.01.2022 267 293 KRW KRW_202201 01.01.2022 -6664676
31 2022001 31.01.2022 268 293 KRW KRW_202201 01.01.2022 -12622489
93 2022001 22.12.2021 268 293 KRW KRW_202201 01.01.2022 2821560
84 2022002 31.01.2022 267 293 KRW KRW_202202 01.02.2022 6664676
75 2022002 08.02.2022 267 293 KRW KRW_202202 01.02.2022 -7353723
84 2022002 28.02.2022 268 293 KRW KRW_202202 01.02.2022 -21363519
86 2022002 31.01.2022 268 293 KRW KRW_202202 01.02.2022 12622489
42 2022002 28.02.2022 268 293 KRW KRW_202202 01.02.2022 -1509144
74 2022003 21.03.2022 267 293 KRW KRW_202203 01.03.2022 -28083510
45 2022003 28.02.2022 267 293 KRW KRW_202203 01.03.2022 1509144
30 2022003 28.02.2022 268 293 KRW KRW_202203 01.03.2022 21363519
38 2022003 21.03.2022 268 293 KRW KRW_202203 01.03.2022 -3147707
31 2022002 07.02.2022 267 293 PHP PHP_202202 01.02.2022 -325498,33
11 2022004 07.04.2022 267 293 PHP PHP_202204 01.04.2022 -620217,5
20 2022008 07.02.2022 268 293 PHP PHP_202208 01.08.2022 -527250
9 2022008 18.08.2022 268 293 PHP PHP_202208 01.08.2022 -1048800
79 2022008 07.02.2022 268 293 PHP PHP_202208 01.08.2022 325498,33

Exchange rate:

PK_ExchangeRate TimeId CurrencyCode Exchange Rate
KRW_202201 202201 KRW 0,000739
KRW_202202 202202 KRW 0,000737
KRW_202203 202203 KRW 0,000739
KRW_202204 202204 KRW 0,000741
KRW_202205 202205 KRW 0,000742
KRW_202206 202206 KRW 0,000742
KRW_202207 202207 KRW 0,000743
KRW_202208 202208 KRW 0,000744
KRW_202209 202209 KRW 0,000744
KRW_202210 202210 KRW 0,000744
KRW_202211 202211 KRW 0,000744
KRW_202212 202212 KRW 0,000744
PHP_202201 202201 PHP 0,017241
PHP_202202 202202 PHP 0,017218
PHP_202203 202203 PHP 0,017289
PHP_202204 202204 PHP 0,017397
PHP_202205 202205 PHP 0,017529
PHP_202206 202206 PHP 0,017545
PHP_202207 202207 PHP 0,017548
PHP_202208 202208 PHP 0,017566
PHP_202209 202209 PHP 0,017566
PHP_202210 202210 PHP 0,017566
PHP_202211 202211 PHP 0,017566
PHP_202212 202212 PHP 0,017566

[PaulDBrown]

Thanks for the messages. I'm getting a security warning (basically the download of the PBIX file is blocked). Can you try uploading to a cloud service such as OneDrive, Google Drive, iCloud or a servcie such as https://wetransfer.com/  ?

[Ale]

Hi @PaulDBrown , I just uploaded to the WeTransfer as you mentioned. Here is the link: https://we.tl/t-eaiQglZWg0

Let me know if the link works. And thank you so much in advance for the support.

[PaulDBrown · Accepted solution]

Thanks for the file. Ok, to make things simple, we need to alter the model as follows.

1) Create a Period Table using:

Year Period Table = SUMMARIZE('Date Table', 'Date Table'[YearMonth], 'Date Table'[Year-Month])

2) Create a field in the fact table for YearMonth Using:

Period = 
YEAR(fTable[FK_Date])*100 + MONTH(fTable[FK_Date])

3) Change the relationship between the date table and the ftable to inactive; create relationships between the Year Period Table and the corresponding fields in the Date Table and the Exchange Rate Dimension Table. The model looks like this:

 Now create the following measures:

SUM LC Value = 
SUM(fTable[Value (LC)])

YTD LC =
CALCULATE (
    [SUM LC Value],
    DATESYTD ( 'Date Table'[Date] ),
    USERELATIONSHIP ( 'Date Table'[Date], fTable[FK_Date] )
)

Average Exchange rate =
IF (
    ISINSCOPE ( 'Exchange rate dimension'[CurrencyCode] ),
    AVERAGE ( 'Exchange rate dimension'[Exchange Rate] )
)

Totals by Period =
SUMX (
    SUMMARIZE (
        'Exchange rate dimension',
        'Year Period Table'[Year-Month],
        'Exchange rate dimension'[CurrencyCode]
    ),
    [Average Exchange rate] * [YTD LC]
)

set up the visuals using the Year-Month Field from the Year Period Table, and add the measure and you will get:

Sample PBIX file attached

[Ale]

Works perfectly!

Thank you so much, Paul! I will take some minutes to understand the whole logic, but the results seem to be 100% correct now. Have a great week ahead!