Power Query Dynamic Date Filter With Intermittent Dates

[ndbennett]

I am creating a sales dashboard and I want to compare the last 7 days of available sales data against a weekly average.

My problem is that the timing of the availability of the sales data is not that reliable. At best it could be 3 days late but sometimes it is 5 or 6 days late. Therefore I would like to create a dynamic filter in Power Query to report only data from the most recently uploaded day plus the 6 days prior to that, regardless of the current date. Is this possible? Thanks!

 

[GraH]

Hi, yes that's possible. You need to declare a variable.

Something like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetTypes = Table.TransformColumnTypes(Source,{{"Dates", type date}, {"Value", type number}, {"Weeknum", Int64.Type}}),
    RefDate = Date.AddDays(List.Max(SetTypes[Dates]),-7),
    FilterSetOnRefDate = Table.SelectRows(SetTypes, each [Dates] > RefDate)
in
    FilterSetOnRefDate

Given this sample data

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

this table is returned by the query

Dates	Value	Weeknum
31/10/2021	$ 69,17	43
31/10/2021	$ 83,53	43
1/11/2021	$ 89,21	44
4/11/2021	$ 67,33	44
1/11/2021	$ 99,28	44

 

[ndbennett]

Thank you for your response. It worked perfectly! I am very grateful for your help.

 

[GraH]

Great to read it's working. Thanks for this feedback.