Forward-looking date filter by N days

[NuMatt21114]

I've been searching for an answer for this for days with no joy. I sure hope someone here can help.

I'm trying to filter my data based on dates. Power Query makes it easy enough to filter the Last N Days or the Next N Days. But I need to go beyond that. I need to filter by a range of dates, specifically in the future. As an example, I want to see everything that is 14-28 days in the future. PQ allows you to do Between, but you have to specify dates, not number of days, as your bookends. I've tried In The Next 28 days combined with After, but After also allows only dates. I need this to be based on # of days so that it is dynamic, updating automatically each day. I need to avoid having to input dates each time I want to refresh the data.

Thanks in advance for any help.

Matt

 

[GraH]

You can use the trick "between" like you described. Then simply update the M-formula.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Dates] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()),14) and [Dates] <= Date.AddDays(DateTime.Date(DateTime.LocalNow()),28))
in
    #"Filtered Rows"

For a simple source sample

Book1
	D
2	Dates
3	2/06/2022
4	3/06/2022
5	4/06/2022
6	5/06/2022
7	6/06/2022
8	7/06/2022
9	8/06/2022
10	9/06/2022
11	10/06/2022
12	11/06/2022
13	12/06/2022
14	13/06/2022
15	14/06/2022
16	15/06/2022
17	16/06/2022
18	17/06/2022
19	18/06/2022
20	19/06/2022
21	20/06/2022
22	21/06/2022
23	22/06/2022
24	23/06/2022
25	24/06/2022
26	25/06/2022
27	26/06/2022
28	27/06/2022
29	28/06/2022
30	29/06/2022
31	30/06/2022
32	1/07/2022
33	2/07/2022
34	3/07/2022
35	4/07/2022
36	5/07/2022
37	6/07/2022
38	7/07/2022
39	8/07/2022
40	9/07/2022
41	10/07/2022
42	11/07/2022
43	12/07/2022
44	13/07/2022
45	14/07/2022
46	15/07/2022
47	16/07/2022
48	17/07/2022
49	18/07/2022
50	19/07/2022
51	20/07/2022
52	21/07/2022
53	22/07/2022
54	23/07/2022
55	24/07/2022
56	25/07/2022
57	26/07/2022
58	27/07/2022
59	28/07/2022
60	29/07/2022
61	30/07/2022
62	31/07/2022
63	1/08/2022
64	2/08/2022
65	3/08/2022
66	4/08/2022
67	5/08/2022
68	6/08/2022
69	7/08/2022
70	8/08/2022
71	9/08/2022
72	10/08/2022
73	11/08/2022
74	12/08/2022
75	13/08/2022
76	14/08/2022
77	15/08/2022
78	16/08/2022
79	17/08/2022
80	18/08/2022
81	19/08/2022
82	20/08/2022
83	21/08/2022
84	22/08/2022
85	23/08/2022
86	24/08/2022
87	25/08/2022
88	26/08/2022
89	27/08/2022
90	28/08/2022
91	29/08/2022
92	30/08/2022
93	31/08/2022
94	1/09/2022
95	2/09/2022
96	3/09/2022
97	4/09/2022
98	5/09/2022
99	6/09/2022
100	7/09/2022
101	8/09/2022
102	9/09/2022
Sheet1

 

[NuMatt21114]

G.,

Thanks so much for that. I hadn't been able to nail down the M language necessary, but this worked perfectly. Exactly what I needed!

Matt

You can use the trick "between" like you described. Then simply update the M-formula.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Dates] >= Date.AddDays(DateTime.Date(DateTime.LocalNow()),14) and [Dates] <= Date.AddDays(DateTime.Date(DateTime.LocalNow()),28))
in
    #"Filtered Rows"

[/QUOTE]

 

[GraH]

Thanks for the feedback. Glad to help.