Dynamically filter to current week

[Deano478]

Hi all,

I feel this is easier than i'm thinking it is but I just cant get my head around it. essentially I have a list of dates like so in a column and what I need to is essentially dynamically filter these dates to the current week only rather than having to do is manually every week.

TheDate

01/01/2024

02/01/2024

03/01/2024

04/01/2024

05/01/2024

06/01/2024

07/01/2024

08/01/2024

09/01/2024

10/01/2024

11/01/2024

12/01/2024

13/01/2024

14/01/2024

15/01/2024

16/01/2024

17/01/2024

18/01/2024

19/01/2024

20/01/2024

21/01/2024

22/01/2024

23/01/2024

24/01/2024

25/01/2024

26/01/2024

27/01/2024

28/01/2024

29/01/2024

30/01/2024

31/01/2024

01/02/2024

02/02/2024

03/02/2024

04/02/2024

05/02/2024

06/02/2024

07/02/2024

08/02/2024

09/02/2024

10/02/2024

11/02/2024

12/02/2024

13/02/2024

14/02/2024

15/02/2024

16/02/2024

17/02/2024

18/02/2024

19/02/2024

20/02/2024

21/02/2024

22/02/2024

23/02/2024

24/02/2024

25/02/2024

26/02/2024

27/02/2024

28/02/2024

29/02/2024

01/03/2024

02/03/2024

03/03/2024

04/03/2024

05/03/2024

06/03/2024

07/03/2024

08/03/2024

09/03/2024

10/03/2024

11/03/2024

12/03/2024

13/03/2024

14/03/2024

15/03/2024

16/03/2024

17/03/2024

18/03/2024

19/03/2024

20/03/2024

21/03/2024

22/03/2024

23/03/2024

24/03/2024

25/03/2024

26/03/2024

27/03/2024

28/03/2024

29/03/2024

30/03/2024

31/03/2024

01/04/2024

02/04/2024

03/04/2024

04/04/2024

05/04/2024

06/04/2024

07/04/2024

08/04/2024

09/04/2024

10/04/2024

11/04/2024

12/04/2024

13/04/2024

14/04/2024

15/04/2024

16/04/2024

17/04/2024

18/04/2024

19/04/2024

20/04/2024

21/04/2024

22/04/2024

23/04/2024

24/04/2024

25/04/2024

26/04/2024

27/04/2024

28/04/2024

29/04/2024

30/04/2024

01/05/2024

02/05/2024

03/05/2024

04/05/2024

05/05/2024

06/05/2024

07/05/2024

08/05/2024

09/05/2024

10/05/2024

11/05/2024

12/05/2024

13/05/2024

14/05/2024

15/05/2024

16/05/2024

17/05/2024

18/05/2024

19/05/2024

20/05/2024

21/05/2024

22/05/2024

23/05/2024

24/05/2024

25/05/2024

26/05/2024

27/05/2024

28/05/2024

29/05/2024

30/05/2024

31/05/2024

01/06/2024

02/06/2024

03/06/2024

04/06/2024

05/06/2024

06/06/2024

07/06/2024

08/06/2024

09/06/2024

10/06/2024

11/06/2024

12/06/2024

13/06/2024

14/06/2024

15/06/2024

16/06/2024

17/06/2024

18/06/2024

19/06/2024

20/06/2024

21/06/2024

22/06/2024

23/06/2024

24/06/2024

25/06/2024

26/06/2024

27/06/2024

28/06/2024

29/06/2024

30/06/2024

01/07/2024

02/07/2024

03/07/2024

04/07/2024

05/07/2024

06/07/2024

07/07/2024

08/07/2024

09/07/2024

10/07/2024

11/07/2024

12/07/2024

13/07/2024

14/07/2024

15/07/2024

16/07/2024

17/07/2024

18/07/2024

19/07/2024

20/07/2024

21/07/2024

22/07/2024

23/07/2024

24/07/2024

25/07/2024

26/07/2024

27/07/2024

28/07/2024

29/07/2024

30/07/2024

31/07/2024

01/08/2024

02/08/2024

03/08/2024

04/08/2024

05/08/2024

06/08/2024

07/08/2024

08/08/2024

09/08/2024

10/08/2024

11/08/2024

12/08/2024

13/08/2024

14/08/2024

15/08/2024

16/08/2024

17/08/2024

18/08/2024

19/08/2024

20/08/2024

21/08/2024

22/08/2024

23/08/2024

24/08/2024

25/08/2024

26/08/2024

27/08/2024

28/08/2024

29/08/2024

30/08/2024

31/08/2024

01/09/2024

02/09/2024

03/09/2024

04/09/2024

05/09/2024

06/09/2024

07/09/2024

08/09/2024

09/09/2024

10/09/2024

11/09/2024

12/09/2024

13/09/2024

14/09/2024

15/09/2024

16/09/2024

17/09/2024

18/09/2024

19/09/2024

20/09/2024

21/09/2024

22/09/2024

23/09/2024

24/09/2024

25/09/2024

26/09/2024

27/09/2024

28/09/2024

29/09/2024

30/09/2024

01/10/2024

02/10/2024

03/10/2024

04/10/2024

05/10/2024

06/10/2024

07/10/2024

08/10/2024

09/10/2024

10/10/2024

11/10/2024

12/10/2024

13/10/2024

14/10/2024

15/10/2024

16/10/2024

17/10/2024

18/10/2024

19/10/2024

20/10/2024

21/10/2024

22/10/2024

23/10/2024

24/10/2024

25/10/2024

26/10/2024

27/10/2024

28/10/2024

29/10/2024

30/10/2024

31/10/2024

01/11/2024

02/11/2024

03/11/2024

04/11/2024

05/11/2024

06/11/2024

07/11/2024

08/11/2024

09/11/2024

10/11/2024

11/11/2024

12/11/2024

13/11/2024

14/11/2024

15/11/2024

16/11/2024

17/11/2024

18/11/2024

19/11/2024

20/11/2024

21/11/2024

22/11/2024

23/11/2024

24/11/2024

25/11/2024

26/11/2024

27/11/2024

28/11/2024

29/11/2024

30/11/2024

01/12/2024

02/12/2024

03/12/2024

04/12/2024

05/12/2024

06/12/2024

07/12/2024

08/12/2024

09/12/2024

10/12/2024

11/12/2024

12/12/2024

13/12/2024

14/12/2024

15/12/2024

16/12/2024

17/12/2024

18/12/2024

19/12/2024

20/12/2024

21/12/2024

22/12/2024

23/12/2024

24/12/2024

25/12/2024

26/12/2024

27/12/2024

28/12/2024

29/12/2024

30/12/2024

31/12/2024

Huge thanks in advance to anyone who sees this 

[alexnajm · Accepted solution]

You can either use %W or %U depending on when the week starts: DateTime Functions (alteryx.com)! This workflow works.

[binuacs · Accepted solution]

@Deano478 can you try 

DateTimeFormat([TheDate],'%W') = DateTimeFormat(DateTimeToday(),'%W')
AND
DateTimeFormat([TheDate],'%Y') = DateTimeFormat(DateTimeToday(),'%Y')

[Deano478]

hey @binuacs  and @alexnajm  many thanks I tried an approach with %W and %U and both went well so many thanks guys

[Deano478]

Het @binuacs  and @alexnajm I know we fixed the initial query yesterday but im also wondering how could I filter from the previous Saturday to the Friday of this current week?

[alexnajm]

Since we are calculating numbers, you can convert ToNumber and minus 1 to get the previous weeks' week number

[Deano478]

Hey @alexnajm I'll be honest I dont fully understand your logic here is the filter I'm currently using to get just Monday to Friday of the current week:

(DateTimeFormat([TheDate],'%a') != 'Sat' && DateTimeFormat([TheDate],'%a') != 'Sun') && DateTimeFormat([TheDate],'%U') = DateTimeFormat(DateTimeNow(),'%U')

[alexnajm]

Then I am not sure you can keep the previous Saturday since you are excluding all Saturdays in this logic... can you clarify exactly what you need? And give an example?

[Deano478]

Realistically I've 2 filters one that will just capture Monday to Friday like so:

(DateTimeFormat([TheDate],'%a') != 'Sat' && DateTimeFormat([TheDate],'%a') != 'Sun') && DateTimeFormat([TheDate],'%U') = DateTimeFormat(DateTimeNow(),'%U')

and another filter like so that going from Monday to Sunday of the current week like so:

DateTimeFormat([TheDate],'%W') = DateTimeFormat(DateTimeToday(),'%W')
AND
DateTimeFormat([TheDate],'%Y') = DateTimeFormat(DateTimeToday(),'%Y')

So for the second filter above I would get:

2024-04-29
2024-04-30
2024-05-01
2024-05-02
2024-05-03
2024-05-04
2024-05-05

What I need to aim get is this which would be last Saturday to this Friday:

2024-04-27

2024-04-28

2024-04-29

2024-04-30

2024-05-01

2024-05-02

2024-05-03

[alexnajm]

Then what I might look at doing is finding the upcoming Friday using a Formula or a Generate Rows, and then filter the days between that date and 6 days back (aka the previous Saturday)!

Otherwise I would look at starting a new thread since this is a new question from the original post - please include sample data and/or a workflow if possible too.