Making filter or dynamic

[Deano478]

Hi all,

I currently have a manual filter like so that I have to keep changing every week:

[StartDate] >= "2023-01-01" AND
[StartDate] <= "2023-11-13"

What I want to id make this filter a bit more dynamic meaning instead, the second condition should always be the Monday of the current week so instead of me having to manually change it i was hoping to put some logic behind it so that it can do dynamically itself. The first part will never change but the second condition will go to the current week i.e for next week manually it would be: 

[StartDate] >= "2023-01-01" AND
[StartDate] <= "2023-11-20"

 I've tried various approaches with the DateTime function but no dice yet any help would be greatly appreciated 

[DataNath · Accepted solution]

@Deano478 definitely feels like there's a more elegant expression here but this will dynamically give you the Monday of the current week so you can just replace your "2023-11-13"/"2023-11-20" value with this:

ToDate(
DateTimeAdd(DateTimeToday(),
-(ToNumber(
DateTimeFormat(
DateTimeToday(),'%u'))-1),
'day'))

[Deano478]

@DataNath  Cheers for taking the time to have a look at this for me just so i can be clear in my own mind what odes this expression do?😀

[DataNath]

No problem @Deano478 - I can appreciate it looks a bit of a mess!

1. DateTimeFormat() with the %u argument returns the date's day of the week (1-7 with 1 being Monday)
2. The above results in a string i.e. today would be '2', hence we wrap this in ToNumber()
3. We need this as a number as it's going to be used in the DateTimeAdd() calc
4. To get back to Monday we'll always want to take the numeric day of the week -1, hence the -1 after the ToNumber()
5. The - before wrapped parentheses is because in the DateTimeAdd() calc, we want to go backwards rather than forwards
6. DateTimeAdd() just takes away N days where N is what we've calc'd above
7. ToDate() is used to force the result into a date as the outcome of DateTime... functions will always give a DateTime data type which leads to issues with comparisons against pure dates

Hope this helps!

[Deano478]

@DataNath seeing it like that makes a lot of sense many thanks for taking the time again to explain the expression it makes a lot more sense to me now.