How to lookup value from another table in date range, using date?

[stranter]

Hi,

Sorry for posting what looks like a commonly asked question, but I can't seem to find the right answer.  I have two (unrelated) tables:

registrations

event_date

email

statuses

created_date

email

status_value

I want to add some columns to registrations:

completed_status_at_event_date

This column need to take the email from registrations, lookup to statuses and return a 1 if the status_value - "Completed" has a created_date before the event_date

completed_status_14days_after_event_date

This column need to take the email from registrations, lookup to statuses and return a 1 if the status_value - "Completed" has a created_date between the event_date and 14 days after.

I just seem to be skirting round the solution and going round in circles.

Hope someone can help.

Thanks,

Stewart

[Anonymous]

@stranter I think you do have related tables.. your relation is the email in each table.

Link the 2 tables up with their email and you'll be able to create some simple formulas.

Should look something like this:

completed_status_at_event_date = IF(RELATED('Statuses'[status_value]="Completed" && RELATED('Statuses'[created_date])<'Registrations'[event_date],1,0)

completed_status_14days_after_event_date = IF(RELATED('Statuses'[status_value]="Completed" && RELATED('Statuses'[created_date]) >='Registrations'[event_date] && RELATED('Statuses'[created_date]) <='Registrations'[event_date]+14,1,0)

[stranter · Accepted solution]

Thanks for the reply.  I think I worked out the solution using this forum post, and it seems to work!

CompleteCustomer = 
VAR EventDate = 'Registrations'[EventDateAsDate]
VAR RegEmail = 'Registrations'[Email]
RETURN
	IF (
		CALCULATE (
			COUNTROWS('Statuses'),
			FILTER (
				'Statuses',
				'Statuses'[Created On] < EventDate &&
				ISERROR(SEARCH("Complete", 'Statuses'[CompleteStatus])) &&
				'Statuses'[Email] = RegEmail
			)
		)
		> 0,
		"Customer Complete",
		"Customer Active"
	)