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 ...
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

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