Forum Discussion

stranter's avatar
stranter
Frequent Visitor
7 years ago
Solved

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

 

 

  • stranter's avatar
    stranter
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      stranter
      Frequent Visitor
      7 years ago

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