Forum Discussion
Join on Date Range
Hello everyone,
I've been struglling with the following issue for a few days and hope someone might be able to help.
I'm trying to merge 2 tables on date range without success.
The first table shows a list users and when emails were sent to them:
| Table 1 | ||
| User | Email_Date | Email_Date_7days |
| 1 | 12/07/2016 | 19/07/2016 |
| 1 | 13/10/2016 | 20/10/2016 |
| 2 | 12/07/2016 | 19/07/2016 |
| 3 | 25/07/2016 | 01/08/2016 |
| 3 | 19/12/2016 | 26/12/2016 |
| 4 | 12/07/2016 | 19/07/2016 |
| 5 | 11/08/2016 | 18/08/2016 |
The 2nd table is a list of when they responded:
| Table 2 | |
| User | Response_Date |
| 1 | 13/07/2016 |
| 1 | 15/07/2016 |
| 2 | 15/07/2016 |
| 2 | 21/07/2016 |
| 3 | 01/08/2016 |
I want table 1 to show the number of responses within 7 days, such that:
| Table 1 | |||
| User | Email_Date | Email_Date_7days | Responses_7days |
| 1 | 12/07/2016 | 19/07/2016 | 2 |
| 1 | 13/10/2016 | 20/10/2016 | 0 |
| 2 | 12/07/2016 | 19/07/2016 | 1 |
| 3 | 25/07/2016 | 01/08/2016 | 1 |
| 3 | 19/12/2016 | 26/12/2016 | 0 |
| 4 | 12/07/2016 | 19/07/2016 | 0 |
| 5 | 11/08/2016 | 18/08/2016 | 0 |
I would appreciate some giudance in implementin this in Power Query(M)
Why do you need to do this is Power Query? We can get this result in DAX easily.
Column = CALCULATE(COUNT(Table2[User]),FILTER(Table2,Table2[User]=Table1[User]&&Table2[Response_Date]>=Table1[Email_Date]&&Table2[Response_Date]<=Table1[Email_Date_7days]))
Regards,
Charlie Liao
2 Replies
Why do you need to do this is Power Query? We can get this result in DAX easily.
Column = CALCULATE(COUNT(Table2[User]),FILTER(Table2,Table2[User]=Table1[User]&&Table2[Response_Date]>=Table1[Email_Date]&&Table2[Response_Date]<=Table1[Email_Date_7days]))
Regards,
Charlie Liao
Thanks Charlie. That worked.
