cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stranter Frequent Visitor
Frequent Visitor

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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
stranter Frequent Visitor
Frequent Visitor

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

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"
	)
2 REPLIES 2
jd009 Member
Member

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

@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)

 

Highlighted
stranter Frequent Visitor
Frequent Visitor

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

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"
	)