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.
