How to: Multi conditional table join/lookup, DAX or M?

Hi,

I have two tables that I want to use together, but I'm not sure what's the best approach.

• First table is my sales table, with one line for every single line in an invoice.
• Second is a table of members in our subscription club, were every member has a line for every day, so I'm able to get the "membership" status on any given day.
• Both tables have a CustomerKey as identifier.

In the sales table, I wan't to retrieve the membership status on the day of the invoice, so I'm for example able to select all "active" members for November and get shown their sales.

i.e. I need to identify the customer through the customerKey and also through the specific date, so I get the correct subscription status on the day of the invoice.

To get shown the development in our subscription club, I've made the following measure, from which I was hoping to use to select customers, and is the one I'm using in the picture below.

Antal Club medlemmer = 
CALCULATE(
    COUNTROWS('Membership Status');
    LASTDATE('Membership Status'[Date]
	)
)

I've tried to solve it by making a relationship between the membership table and our customer table, which is then related to the sales table, which doesn't seem to work, as PowerBI displayed all the different kind og status on every invoice, as shown in the picture below.

How do I solve this, so it only shows revenues with the correct status?

Can be either in DAX or Query.