Hello,
I am trying to calculate rate based of product site visits and products purchased which will be products purchased in that last 15 days over product site visits for the same time frame. However, the 15day look back timeframe should be dynamic for any date selected.
I wrote a condition to filter the data which is [SiteVisitDate]>=DatetimeAdd(tDateTimeToday(),-15,"days") and [SiteVisitDate]<=DateTimeToday() but this seem to capture or filter data for today only- and when I link the output to a dashboard and select a date from drop down this is not pulling data correctly.as it has data limited to last 15 days. So when I select yesterday's day I am not showing full data.
Is this possible to do this dynamically for any date?
Sample data
| SiteVisit | Purchased | SiteVisitDate |
| 1 | 0 | 02/17/2023 |
| 1 | 0 | 09/16/2022 |
| 1 | 0 | 05/24/2023 |
| 1 | 0 | 09/06/2022 |
| 1 | 0 | 08/26/2020 |
| 1 | 1 | 01/03/2023 |
| 1 | 1 | 08/01/2023 |
| 1 | 0 | 06/23/2023 |
| 1 | 0 | 09/11/2023 |
| 1 | 1 | 04/20/2023 |
| 1 | 0 | 05/09/2023 |
| 1 | 0 | 07/28/2023 |
| 1 | 0 | 11/15/2022 |
| 1 | 1 | 11/08/2022 |
| 1 | 0 | 08/05/2023 |
| 1 | 0 | 05/04/2022 |
| 1 | 0 | 10/21/2022 |
| 1 | 0 | 11/17/2022 |
| 1 | 0 | 07/06/2021 |
| 1 | 0 | 10/03/2022 |
| 1 | 0 | 09/21/2022 |
