Need Help with Advanced DAX Calc

[DAXStudent]

I have some complex requirements for a DAX formula that I can't quite figure out.

Cases are attributed to Counties. That Case -> County attribution changes over time. The Case table can have new rows every several months, but it is not regular and cannot be depended on.

I need a matrix with the average age of the case on the values, county on the rows, and month on the columns. The age of the case needs to 'carry forward' and appear in each month until the next entry in the Case table.

This must be a DAX solution, it cannot be a Power Query solution. Relationships between tables can be changed or removed if necessary for the solution.

Here's my sample data:

Here's the required result:

Here is a link to download my pbix: https://www.dropbox.com/scl/fi/vn7cgah4ru4zilnvm3580/20250422-Power-BI-DAX-Question.pbix?rlkey=tinuh...

[DAXStudent]

That is the most amazing thing I've ever seen. I tip my hat to you.

[lbendlin · Accepted solution]

You need to disconnect all tables and then work it from the perspective of the case, looking up.