Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ale
Resolver II
Resolver II

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

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.

 

Ale_2-1663852697166.png

 

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:

DocumentKeyYearPeriodDocumentDatePostingIdAccountIdCurrencyFK_ExchangeRateFK_DateValue (LC)
3620220012021-11-04267293KRWKRW_2022012022-01-013192
4320220012021-12-22267293KRWKRW_2022012022-01-012100
3120220012022-01-31268293KRWKRW_2022012022-01-01-9333,33
9320220012022-01-31268293KRWKRW_2022012022-01-01-4928
8420220022022-01-31267293KRWKRW_2022022022-02-014928
7520220022022-01-31267293KRWKRW_2022022022-02-019333,33
8420220022022-02-08268293KRWKRW_2022022022-02-01-5376
8620220022022-02-28268293KRWKRW_2022022022-02-01-1120,84
4220220022022-02-28268293KRWKRW_2022022022-02-01-15866,7
7420220032022-02-28267293KRWKRW_2022032022-03-0115866,67
4520220032022-02-28267293KRWKRW_2022032022-03-011120,84
3020220032022-03-21268293KRWKRW_2022032022-03-01-2353,76
3820220032022-03-21268293KRWKRW_2022032022-03-01-21000

 

Exchange Rate table dimension:

PK_ExchangeRateTimeIdCurrencyCodeExchange Rate
KRW_202201202201KRW0,000738764965826801
KRW_202202202202KRW0,000737456627871163
KRW_202203202203KRW0,000739485324721465
KRW_202204202204KRW0,000741332021669876
KRW_202205202205KRW0,000742348926963949
KRW_202206202206KRW0,000741929861957272
KRW_202207202207KRW0,000743168871622698
KRW_202208202208KRW0,000743740317058877
KRW_202209202209KRW0,000743740317058877
KRW_202210202210KRW0,000743740317058877
KRW_202211202211KRW0,000743740317058877
KRW_202212202212KRW0,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.

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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

yearmonth ftable.png

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:

model.png

 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:

result.png

 

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

11 REPLIES 11
PaulDBrown
Community Champion
Community Champion

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

result.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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
Community Champion
Community Champion

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

model.png

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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
Community Champion
Community Champion

Any chance you can provide more complete sample data?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

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

 

Values.PNG

 

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
Community Champion
Community Champion

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/  ?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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
Community Champion
Community Champion

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

yearmonth ftable.png

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:

model.png

 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:

result.png

 

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.