Recalculating daily balances using FX rates table

[Aneta_G]

Hi All,

I am working on a report showing daily cash balances. I have three tables in my data model:
a) table with daily transactions in different entities and in different currencies (BANK_NAV_ALL)
b) dates table (DATES)
c) FX table (where daily FX rates are stored, period covered: 01/01/2013 - 31/07/2023) (MONTH_END_RATES)

Table a) is connected with table b) by date and similarly, table c) is also connected with table b) by date.
My goal is to recalculate balances in different currencies from table a) to USD using FX rates from table c). Daily balances are cumulative, i.e. when I want to calculate a balance in a given currency as of today, I have to sum up all the entries up to that date.
I prepared a simple table in which I want to lookup a rate for a selected currency and selected date. I am using LOOKUPVALUE function, which looks as below:

LOOKUPVALUE( MONTH_END_RATES[Month-end FX rate],
MONTH_END_RATES[Date], VALUES( DATES[Date] ) ,
MONTH_END_RATES[Currency], VALUES(BANK_NAV_ALL[LCY] ) )

And the results are shown in the table below:

As you can see, there are many blanks in the table. I was wondering why is that but discovered that I get blanks if there are no entries booked with a given date. For example, there were no any bookings for CZK on 31st July 2023. The problem is that I would like to show a balance for that date, so I need to find a proper FX rate.
Is there any way to display FX rates values for each date, despite the fact that table a) does not contain entries under each date and each currency?

Many thanks for any help!

[ystroman]

Hello @Aneta_G and thank you for starting a conversation in the Community Forum. To allow members to assess the state, would you please add your work-in-progress Power BI Desktop File, the source data file in Excel format, any DAX measures formatted for the reader, and add a description and images of your desired result.

Please also ensure that the data model includes a dedicated Date table to accommodate any time intelligence efforts.

Are there any additional thoughts or questions which you would like to add that may be helpful to the reader who is not familiar with your line of business?

[Aneta_G]

Hi @ystroman,

Thanks for your reply.

I am sharing my work in progress PBI file and the source data in excel format.
This is the result I would like to achieve, i.e. find FX rate for a given currency, for a given day:

Unfortunately, when using the measure FX rate which is in my model, I get following results:

Any help with this issue will be greatly appreciated.
Source Data.xlsx (6.3 MB)
cash report.pbix (3.8 MB)

Thanks,
Aneta