cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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"
	)

View solution in original post

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)

 

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 1,047 guests
Please welcome our newest community members: