How to calculate rolling 30 days of DateA occurring from DateB

BrandonC

Hi all,

So I had an initial question that has since been answered which was how to calculate when a date occurs within the same month of another date column but has since been answered (https://dojo.domo.com/discussion/comment/56039#Comment_56039).

Now, I would like to change this up to see when column DateA occurs within 30 days of column DateB.

The date calculations escape me but hoping to learn through solutions of what works and how. Any advice is greatly appreciated!

Additional context: I have two date columns and want to use DateB as the "anchor" to see instances of when DateA occurs within +30 days of DateA to grade performance of duties for associates based on when a task was assigned to them.

(e.g.) DateB: 12/1/2021 and DateA 12/25/2021 would be counted as a 1 vs DateA 01/11/2022 would be counted as a zero with the same DateB of 12/1/2021.

MarkSnodgrass

You can accomplish this with the DATEDIFF() function, which calculates the number of days between two dates. Example:

DATEDIFF(`DateA`,`DateB`)

To count how many are within 30 you would add a CASE statement around this, like this:

CASE WHEN DATEDIFF(`DateA`,`DateB`) <= 30 THEN 1 ELSE 0 END