Look up and return a value within specific date range

[Anonymous]

Dear all

I have two tables, order and membership. The membership has the premium membership start date and end date of each customer.

My target is to find the coresponding memebership status (premium or not) based on the order date. If the order date fall into the date range while the customer is a premium member, then return "premium", if not, then return blank.

I found a post related to my question, but merge does not work for big dataset. 

Solved: Re: Lookup value in Power Query between two dates - Microsoft Power BI Community

Is there any other solutions? 

My dummy data is as below:

Order Table

Customer_ID	Order_Date
A0001	9/10/2020
A0001	9/12/2020
A0001	10/3/2020
A0001	10/28/2020
A0001	10/31/2020
A0001	11/5/2020
A0001	12/12/2020

Membership Table

Customer_ID	Premium_Start_Date	Premium_End_Date
A0001	9/1/2020	9/30/2020
A0001	10/15/2020	11/15/2020

Expecting Result

Customer_ID	Order_Date	Member_Status
A0001	9/10/2020	Premium
A0001	9/12/2020	Premium
A0001	10/3/2020
A0001	10/28/2020	Premium
A0001	10/31/2020	Premium
A0001	11/5/2020	Premium
A0001	12/12/2020

Thank you in advance!

[HoangHugo]

Hi,

try this one

Member_status = if(COUNTROW(CALCULATEABLE(Membership, [Premium Stardate]<EARLIER[Order Date],[Premium End Date]>EARLIER[Order Date])>=1,"Premium",Blank())

[Anonymous]

Hi @HoangHugo , thanks for your reply.

Even I only have one customer in my dummy data, I actually have more than one customer in both tables, which means that the solution you shared may not work, because the customer IDs have to match.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

Member_Status = 
VAR _sdate =
    CALCULATE (
        MAX ( 'Membership'[Premium_Start_Date] ),
        FILTER (
            'Membership',
            'Membership'[Customer_ID] = EARLIER ( 'Order'[Customer_ID] )
                && EARLIER ( 'Order'[Order_Date] ) >= 'Membership'[Premium_Start_Date]
                && EARLIER ( 'Order'[Order_Date] ) <= 'Membership'[Premium_End_Date]
        )
    )
VAR _edate =
    CALCULATE (
        MIN ( 'Membership'[Premium_End_Date] ),
        FILTER (
            'Membership',
            'Membership'[Customer_ID] = EARLIER ( 'Order'[Customer_ID] )
                && EARLIER ( 'Order'[Order_Date] ) >= 'Membership'[Premium_Start_Date]
                && EARLIER ( 'Order'[Order_Date] ) <= 'Membership'[Premium_End_Date]
        )
    )
RETURN
    IF (
        'Order'[Order_Date] >= _sdate
            && 'Order'[Order_Date] <= _edate,
        "Premium",
        ""
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards