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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Lookup value between two dates (Currency)

Hi !

 

I have a sales table with an INVOICEDATE field. I also have a currency history table that looks like the below image, with "valid from" and a "valid to" date. I'm trying to get the exchange rate that was effective on each INVOICEDATE of my sales table.

 

Could someone help ?

 

Thank you in advance !

 

Currency table.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Simpliest way is to use Add Column and use List.Dates in Power Query to get all the dates between the two values. 

List.Dates( 
[Valid From],
Duration.Days( [Valid To] - [Valid From] ) +1,
#duration(1, 0,0,0)
)

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Simpliest way is to use Add Column and use List.Dates in Power Query to get all the dates between the two values. 

List.Dates( 
[Valid From],
Duration.Days( [Valid To] - [Valid From] ) +1,
#duration(1, 0,0,0)
)

I'm working on a very similar problem - implemented your solution, but I must be missing a trick.

 

It's now outputting the new column as a list - how do I then go about matching the right value to all dates in that list?

Anonymous
Not applicable

Wow !!!!! That is awesome !!!!!!!! Works perfectly. Thank you so much Nick_M !!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.