You are reading a single comment by @aggi and its replies.
Click here to read the full conversation.
-
Two ways off the top of my head (so details might be off)
1) in powerquery join the fx rates table to a date table so you end up with every day in the date column with null in the fx rate column where there is no rate for the day. Then apply fill down on the fx rate column
2) in PowerPivot use a formula that tests the fx rate for blank(). If true use calculate to filter the date table joined to the fx table to a suitable date (e.g. the max date less than the current date where fx rate is not blank)
muthesius
@aggi
I've got a set of FX rates that are missing some dates. In Excel this would be no trouble, I'd do an xlookup with it matching to closest rather than exact.
However, if I pull this data into the Data Model and create a relationship based on date in Power Pivot, how do I get it to choose the nearest date with a value rather than returning blanks for the dates that aren't present in my FX rates?