cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Guybrush
Regular Visitor

Exchange Rates - Dates missing - How to fill missing dates with values from last valid date?

Hello,

 

I have a very similar issue to this one but I cannot solve it since I'm rather new to Power BI, DAX and the M language etc.:

https://community.powerbi.com/t5/Integrations-with-Files-and/Generate-Missing-Data-via-Script/td-p/1...

 

 

Explanation:

I have an Order table and an ExchangeRates table and I need to match ExchangeRates based on the Currency and the order date.

 

Exchange rates are downloaded from the ECB/EZB (Europäische Zentral Bank) as a CSV or XML file. But the problem is that weekends and holidays are missing in that files. For example the 1.1.2017 is missing.

 

In the Order table I have an order from 1.1.2017 so I cannot lookup the exchange rate for that specific date. The date I need to use is <= order date so in this case it would be 30.12.2016 (it's the last date in the downloaded file).

 

One more difficulty:

The orders can be in different currencies, for example GBP, PLN, CZK etc.

 

Here are the Table definitions:

Exchange Rates:

Currency - Rate - Date

CZK - 27,021 - 30.12.2016

GBP - 0,8694 - 30.12.2016

PLN - 4,3703 - 30.12.2016

 

Order:

OrderID - TotalAmount - Currency - Date

1 - 100 - GBP - 01.01.2017

2 - 200 - PLN - 01.01.2017

 

So in SQL I would do something like that:

SELECT

  ExchangeRates.Rate

FROM

  ExchangeRates

WHERE

  ExchangeRates.Currency = Order.Currency AND

  ExchangeRates.Date <= Order.OrderDate

ORDER BY

  ExchangeRates.Date DESC

 

I think that's not the right approach here though.

 

Filling the missing dates like in the link above in a new calculated table seems to be better but I don't know how to accomplish that.

1 REPLY 1
v-sihou-msft
Microsoft
Microsoft

@Guybrush

 

According to your description, you want to get the most recent exchange rate for every day. Right?

 

In this scenario, you need to limit the date range context in LASTNONBLANK() function to get the last non empty Exchange Rate. You can create measure in either Exchange Rate table or Order table.

 

In Exchange Rate table, you can create a measure like below:

 

Most recent Exchange Rate =
CALCULATE (
    MAX ( 'Table'[Exchange Rate] ),
    LASTNONBLANK (
        DATESBETWEEN ( 'Table'[Date], BLANK (), LASTDATE ( 'Table'[Date] ) ),
        CALCULATE ( COUNT ( 'Table'[Exchange Rate] ) )
    )
)

66.PNG

 

 

You can also create the measure in Order table:

 

Exchange Rate in Use =
CALCULATE (
    MAX ( 'Table'[Exchange Rate] ),
    LASTNONBLANK (
        DATESBETWEEN ( 'Table'[Date], BLANK (), LASTDATE ( 'Order'[Date] ) ),
        CALCULATE ( COUNT ( 'Table'[Exchange Rate] ) )
    )
)

77.PNG

 

 

Regards,

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.