Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.:
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.
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] ) ) ) )
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] ) ) ) )
Regards,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |