Pulling one value from another table based on a set value in another column and between two dates

[Prue]

Hey, i have two tables, one a standard calendar table and the other with varying data in it including a 'dates from' and a 'dates to' column, see image below.

annual targetscalendar

I need to pull the 'target daily rate' value from the annual targets table into the calendar table where the 'drilling type' is underground and only for the dates between those specified in the 'date from' and 'dates to' columns. ie all the dates from the 01/01/2020 - 31/12/2020 should end up with the value 76.29 in the 'underground' column in the calendar table.

If anyone can help me with a formula for this i would be greatly appreciative. 

[FarhanAhmed · Accepted solution]

I think you can do it by

1- create a duplicate table of  Drilliing Table

2- Filter it to Underground

3- Create a New column "{[Date From]..[Date To]}"

4- Expand this column to Rows

5- Merge this table with your calender or use same table (in this case you dont need calendar table because list of dates already created.)

Ref: https://www.poweredsolutions.co/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/

[Prue]

Thank you for that! It does what i need it too! Much appreciated

[FarhanAhmed]

Great to hear that.

Can you please Mark my post as a solution so that other members can benefit from it.

Appreciate your Kudos too!!