Lookup with date between multiple date ranges

[Anonymous]

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.

[kentyler]

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

[Anonymous]

Thanks sir, so how would the lookup now work if I unpivot the table?

Which formula would I need to use to check if an order falls between the start and end date of a campaign?