Forum Discussion
How can I determine whether an order is placed by VIP with VIP history table
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 , 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] )
2 Replies
- amitchandak
Super User
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] )
Thank you very much for your prompt reply! It works!
