topic Re: Lookup with date between multiple date ranges in DAX Commands and Tips

Lookup with date between multiple date ranges

Anonymous — Wed, 11 Dec 2019 00:43:18 GMT

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.

Re: Lookup with date between multiple date ranges

kentyler — Wed, 11 Dec 2019 04:52:36 GMT

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

Re: Lookup with date between multiple date ranges

Anonymous — Wed, 11 Dec 2019 09:08:25 GMT

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?