Forum Discussion
Lookup with date between multiple date ranges
Hi,
I'm running an email campaign analysis and need to lookup a date within a date range and then return the earliest value associated to it (otherwise there are multiple values and I get blank values)
Table 1
| Email Campaign | Start Date | End Date |
| A | 14-Oct | 21-Oct |
| B | 15-Oct | 22-Oct |
| C | 26-Oct | 31-Oct |
Table 2
| Order Date |
| 15-Oct |
| 26-Oct |
27-Oct |
Desired Output
| Order Date | Campaign |
| 15-Oct | A |
| 16-Oct | A |
| 27-Oct | C |
This is because 15-Oct falls within two set out date ranges it takes the Campaign with the earlier Start Date.
I've tried using FIRSTNONBLANK but haven't got the desired result.
Any help would be appreciated.
2 Replies
If you unpovited your first table
Email Campaign Date A start date 14-Oct A end date 21-Oct B start date 26-Oct etc.
Then you could just get the MIN() start date associated with the campaign
rather than having to do multiple passes seeing which start/end date groups your order date fit into
I'm a personal Power BI trainer I learn something every time I answer a question
