Dynamic Date Range Filter to show most recent completed week but also allow user to control dates

[Anonymous]

Hello,

I'm attempting to create a dynamic date range filter in a report, but I'm having difficulties with setting the filter up properly. I would like the report to show the last complete week (Monday through Sunday), but I would like it to automatically update to reflect whatever the last complete week was without the user having to update the filter.

I know there is the Relative date filter type, however, I would rather not use this because the user needs the ability to extend the start and/or end dates if need be.

For example: If the user opened the report today (April 07, 2023), the report would show the date range filter of (03/27/23 through 04/02/23) since that was the most recent completed week Monday-Sunday. However, the user could then extend the start date and/or end date to arbitrary dates if need be (say 02/28/23 through 04/06/23).

Any help would be greatly appreciated.

Thank you,

Paul

Sample data:

Value	Date
1.176091	1/5/2023
1.20412	1/6/2023
1.230449	1/7/2023
1.176091	1/8/2023
1.20412	1/9/2023
1.230449	1/10/2023
1.176091	1/11/2023
1.20412	1/12/2023
1.230449	1/13/2023
1.176091	1/14/2023
1.20412	1/15/2023
1.230449	1/16/2023
1.176091	1/17/2023
1.20412	1/18/2023
1.230449	1/19/2023
1.176091	1/20/2023
1.20412	1/21/2023
1.230449	1/22/2023
1.176091	1/23/2023
1.20412	1/24/2023
1	1/25/2023
2	1/26/2023
3	1/27/2023
4	1/28/2023
5	1/29/2023
6	1/30/2023
7	1/31/2023
8	2/1/2023
9	2/2/2023
10	2/3/2023
11	2/4/2023
12	2/5/2023
13	2/6/2023
14	2/7/2023
15	2/8/2023
16	2/9/2023
17	2/10/2023
18	2/11/2023
19	2/12/2023
20	2/13/2023
21	2/14/2023
22	2/15/2023
23	2/16/2023
24	2/17/2023
25	2/18/2023
26	2/19/2023
27	2/20/2023
28	2/21/2023
29	2/22/2023
30	2/23/2023
31	2/24/2023
32	2/25/2023
33	2/26/2023
34	2/27/2023
35	2/28/2023
36	3/1/2023
37	3/2/2023
38	3/3/2023
39	3/4/2023
40	3/5/2023
41	3/6/2023
42	3/7/2023
43	3/8/2023
44	3/9/2023
45	3/10/2023
46	3/11/2023
47	3/12/2023
48	3/13/2023
49	3/14/2023
50	3/15/2023
51	3/16/2023
52	3/17/2023
53	3/18/2023
54	3/19/2023
55	3/20/2023
56	3/21/2023
57	3/22/2023
58	3/23/2023
59	3/24/2023
60	3/25/2023
61	3/26/2023
62	3/27/2023
63	3/28/2023
64	3/29/2023
65	3/30/2023
66	3/31/2023
67	4/1/2023
68	4/2/2023
69	4/3/2023
70	4/4/2023
71	4/5/2023
72	4/6/2023
73	4/7/2023

[amitchandak · Accepted solution]

@Anonymous , for that you can create independent date table and use that in slicer

example of last 14 day measures

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -14
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

For the Latest value refer

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e

Add date range from above in the latest formula

example

Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[ID] = max(Data1[ID]) && 'Data1'[Date] >=_min && 'Data1'[Date] <=_max ),Data1[Date])
return
CALCULATE(sum(Data1[qty]), filter( (Data1), Data1[ID] = max(Data1[ID]) && Data1[Date] =_max && 'Data1'[Date] >=_min && 'Data1'[Date] <=_max ))
Sum Last Qty = sumx(VALUES(Data1[ID]) , [Last Qty])