Forum Discussion
Data Range between different dates
Hi, I am trying to create a column filter or some type of measurement that will help me calculate the dates between several dates.
I have a historical table that takes a weekly snapshot each Monday. I need to use the Snapshot date and count how many dates were 2 weeks prior and 2 weeks in the future. For example my last snapshot was taken 4/27/20 i need to know how many sites were within my range of 4/13-5/17. then do the same thing for each snapshot date.
The range is base on the begining of the week Monday and End Sunday. I have a dates table already but don't know how to create something where it won't overlap the filter with other snapshots.
| Sample Data | Snapshot | Dates | Cont Dates between Range | Week Start Rang and End | |
| CurrentWeek | 4/27/2020 | 4/13/2020 | 1 | 4/13-5/17 | |
| 4/27/2020 | 4/18/2020 | 1 | |||
| 4/27/2020 | 2/3/2020 | 0 | |||
| 4/27/2020 | 5/20/2020 | 0 | |||
| 4/27/2020 | 5/5/2020 | 1 | |||
| Last Week | 4/20/2020 | 4/6/2020 | 1 | 4/6-5/10 | |
| 4/20/2020 | 4/18/2020 | 1 | |||
| 4/20/2020 | 5/25/2020 | 0 | |||
| 4/20/2020 | 4/27/2020 | 1 | |||
| 4/20/2020 | 4/1/2020 | 1 | |||
| 2 Weeks Ago | 4/13/2020 | 4/12/2020 | 1 | 3/30-5/3 | |
| 4/13/2020 | 3/30/2020 | 1 | |||
| 4/13/2020 | 3/25/2020 | 0 | |||
| 4/13/2020 | 4/26/2020 | 1 | |||
| 4/13/2020 | 5/20/2020 | 0 |
This is what i want as my Matrix table of course it be later be broken out, but just need to get to overall results first.
| Snapshot Date | Total Count |
| 4/27/2020 | 3 |
| 4/20/2020 | 4 |
| 4/13/2020 | 3 |
Thanks in Advanced.
Hi Anonymous ,
I am not clear about your logic, I think the count of 4/20/2020 should be 3
Sample Data Snapshot Dates Cont Dates between Range Week Start Rang and End CurrentWeek 4/27/2020 4/13/2020 1 4/13-5/17 4/27/2020 4/18/2020 1 4/27/2020 2/3/2020 0 4/27/2020 5/20/2020 0 4/27/2020 5/5/2020 1 Last Week 4/20/2020 4/6/2020 1 4/6-5/10 4/20/2020 4/18/2020 1 4/20/2020 5/25/2020 0 4/20/2020 4/27/2020 1 4/20/2020 4/1/2020 1 this should be 0? 2 Weeks Ago 4/13/2020 4/12/2020 1 3/30-5/3 4/13/2020 3/30/2020 1 4/13/2020 3/25/2020 0 4/13/2020 4/26/2020 1 4/13/2020 5/20/2020 0 You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
I am not clear about your logic, I think the count of 4/20/2020 should be 3
Sample Data Snapshot Dates Cont Dates between Range Week Start Rang and End CurrentWeek 4/27/2020 4/13/2020 1 4/13-5/17 4/27/2020 4/18/2020 1 4/27/2020 2/3/2020 0 4/27/2020 5/20/2020 0 4/27/2020 5/5/2020 1 Last Week 4/20/2020 4/6/2020 1 4/6-5/10 4/20/2020 4/18/2020 1 4/20/2020 5/25/2020 0 4/20/2020 4/27/2020 1 4/20/2020 4/1/2020 1 this should be 0? 2 Weeks Ago 4/13/2020 4/12/2020 1 3/30-5/3 4/13/2020 3/30/2020 1 4/13/2020 3/25/2020 0 4/13/2020 4/26/2020 1 4/13/2020 5/20/2020 0 You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.