How can I determine whether an order is placed by VIP with VIP history table

[TerenceM]

Hello, 

I have done my research but couldn't find any similar cases.

My problem is

I have two tables, one is the sales table

Order id	Customer ID	Order Date	Sales Region
1	12345678	2020-11-22	HK
2	23456789	2020-12-23	US

Another one is the VIP History Table

Customer ID	VIP Start Date	VIP End Date	Type of VIP
12345678	2019-01-01	2020-01-01	VIP
12345678	2021-01-01	2022-01-01	VIP
23456789	2020-02-01	2021-02-01	VVIP

What I want to do is to determine whether the order is purchased within the VIP period

So for the first order, it's not a VIP order, because the order date does not fall in the neither VIP period of the customer 12345678

On the other hand, the second order is a VVIP order, because the order date fall into the VVIP period of the customer 23456789.

Thank you in advance!

Best Regards,

Terence

[TerenceM]

Thank you very much for your prompt reply! It works!

[amitchandak · Accepted solution]

@TerenceM , You can get that value in first table and then check for it

a new column in the first table =

maxx(filter(VIP, VIP{Customer ID] = Sales[Customer ID] && Sales[Order Date] >= VIP[Start Date] && Sales[Order Date]<= VIP[End Date]) , VIP[TYPE of VIP] )