Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Look up and return a value within specific date range

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_IDOrder_Date
A00019/10/2020
A00019/12/2020
A000110/3/2020
A000110/28/2020
A000110/31/2020
A000111/5/2020
A000112/12/2020

 

 

Membership Table

 

Customer_IDPremium_Start_DatePremium_End_Date
A00019/1/20209/30/2020
A000110/15/202011/15/2020

 

 

Expecting Result

 

Customer_ID

Order_DateMember_Status
A00019/10/2020Premium
A00019/12/2020Premium
A000110/3/2020 
A000110/28/2020Premium
A000110/31/2020Premium
A000111/5/2020Premium
A000112/12/2020 

 

Thank you in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

  • HoangHugo's avatar
    HoangHugo
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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