BAQ Expression to Pull data in Whole Week Date Range

Today is Friday 11-05-21; I want the previous two weeks data in whole weeks.

Formula for StartDate Beginning of Week Sunday, 2 weeks ago.

GETDATE()-14 = 10-22-21
DATEPART(WEEKDAY, 10-22-21) = 6
-6 + 1 = -5

DATEADD(-5,10-22-21) = 10-17-21 (Sunday) first day of date range.

In the output, see attached, the formula above in a calculated field results in the correct date.
However, when I use it in an expression to filter the BAQ the earliest payroll date I get is 10-18-21.
My filter condition is >=.

I confirmed that there is payroll data on Sunday10-17-21, by pulling data without the formula.

Any thoughts??

There is also a BAQ constant of Constants.LastDayofPrevWeek to use and you could pivot of that – 14 days.


Thanks, I will give it a shot.

That worked. I like easy solutions, but they make feel like I should have found it.