Find the matching date

[ask]

i have 2 tables. I need to find the matching event date from Event date and create another column in Date table as Event date, and calculate how many days between date and event date,  for example: Event Date is 5/27/2017,  All the Date from Date table =< 5/27/2017 and >5/6/2017(which is next event date in the past) will be 5/27/2017 in the new column of Even Date in Date table. I have output result attached in the end. Any advice ? Thanks.

Event Date

4/29/2017

5/6/2017

5/27/2017

Date

5/18/2017

5/17/2017

5/16/2017

5/15/2017

5/15/2017

5/14/2017

5/6/2017

5/5/2017

5/4/2017

5/3/2017

5/2/2017

5/1/2017

4/30/2017

4/29/2017

4/28/2017

4/27/2017

output table:

Date	Event Date	Days
5/18/2017	5/27/2017	-9
5/17/2017	5/27/2017	-10
5/16/2017	5/27/2017	-11
5/15/2017	5/27/2017	-12
5/15/2017	5/27/2017	-12
5/14/2017	5/27/2017	-13
5/6/2017	5/6/2017	0
5/5/2017	5/6/2017	-1
5/4/2017	5/6/2017	-2
5/3/2017	5/6/2017	-3
5/2/2017	5/6/2017	-4
5/1/2017	5/6/2017	-5
4/30/2017	5/6/2017	-6
4/29/2017	4/29/2017	0
4/28/2017	4/29/2017	-1
4/27/2017	4/29/2017	-2

[ask]

waiting ............. 

can anyone help me out?

[v-shex-msft · Accepted solution]

Hi @ask,

You can add calculate column with minx function to get the specific event date, below is the sample:

Logic: get all "event date" which large the current date, use minx function to get the min date.

Event = MINX(FILTER(ALL('Event'),Event[Event Date]>=EARLIER('Date'[Date])),[Event Date])

Diff = DATEDIFF([Date],[Event],DAY)

Regards,

Xiaoxin Sheng

[ask]

Thank you @v-shex-msft. Super helpful

[ask]

I have 2 tables, one event table, another table containing the date as well. I need to find the matching date from event date. For example, 5/18/2017, I need to add 5/27/2017 from Event table, and calculate how many tables from event date. I have the output table attached as well. Any advice?

Event Date

4/29/2017

5/6/2017

5/27/2017

Date

5/18/2017

5/17/2017

5/16/2017

5/15/2017

5/15/2017

5/14/2017

5/6/2017

5/5/2017

5/4/2017

5/3/2017

5/2/2017

5/1/2017

4/30/2017

4/29/2017

4/28/2017

4/27/2017

Output will look like the table below

Date	Event Date	Days
5/18/2017	5/27/2017	-9
5/17/2017	5/27/2017	-10
5/16/2017	5/27/2017	-11
5/15/2017	5/27/2017	-12
5/15/2017	5/27/2017	-12
5/14/2017	5/27/2017	-13
5/6/2017	5/6/2017	0
5/5/2017	5/6/2017	-1
5/4/2017	5/6/2017	-2
5/3/2017	5/6/2017	-3
5/2/2017	5/6/2017	-4
5/1/2017	5/6/2017	-5
4/30/2017	5/6/2017	-6
4/29/2017	4/29/2017	0
4/28/2017	4/29/2017	-1
4/27/2017	4/29/2017	-2