Power Pivot: Match next non-blank item after date match for customer across two tables using DAX

[Phil_T123]

Hi,

I need to create a calculated column in Table_Order that would find the next matching, non-blank Customer Status on or after the current Order Date for the current row Customer #.
The tricky part is when there is no matching Customer Status in Table_Ship for a Customer # on a particular Order Date (please see the orange highlights). The match then needs to be on the next matching Ship Date for the Customer # where the Customer Status is not blank.

Please could somebody help me and show me the DAX?

I've spent a long time Googling and have gotten nowhere. I feel like this should be easy!

Also please bear in mind that these tables are massively simplified versions of my actual work, and any suggestion to reorganise the tables or start from scratch won't help me, I need some DAX please!

Thanks in advance!

Phil.

 

[Phil_T123]

Apologies, I probably should have said that this is for Excel Power Pivot. I can't work out how to edit my original post, thanks.