Many to many relationship

[shoeshirt]

I created a bridge table to join 2 separate tables using Import connectivity so I have a many-to-many relationship between 3 tables.

The Bridge table only has  distinct idnumbers

Capacity has idnumbers, dates and capacities. Capacities can change at any time but for most it stays the same.

Value has idnumbers, dates and volumes going back 1 year.

I want to merge the Capacity and Value tables so that I have the value and the capacity with the capacity changing as the capacity date lines up

with the value date-basically I want the capacity to carry forward until it changes again.

Capacity changes on 1/15/2020

This is so I can show the total value on a line chart as compared to the average of the capacity so that as the values rise you can see it getting close to the capacity but then see the change in the capacity as it is increased in relation to the value. There's probably an easier way to do this than I am visualizing but I can't seem to come up with anything.

Any help would be much appreciated!

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
Time Intelligence - Direct Query

[shoeshirt]

Can I send the .pbix to you directly? 

[shoeshirt]

I ended up finding a solutiion using DAX. Thank you to anyone who may be looking at this now but I ended up solving the problem.