update visual for previous 6 weeks when a date is selected.

[Haydn_R]

DataSet is called: Calls_TFR

In desktop powerbi, I have a field called WeekEnidng (Ends on a Sunday). An example of the values are as follows.

29/10/2023,22/10/2023,15/10/2023,08/10/2023,01/10/2023,24/09/2023,17/09/2023,10/09/2023,03/09/2023,27/08/2023,13/08/2023,06/08/2023,30/07/2023,23/07/2023.

Desires result. Have a filter where I can select a date (this will be in a slicer  or filter with the field WeekEnding used) and based on this selection a visual such as a chart or table (which will have WeekEnding and a numerical field called Transfers) which update so that it only displays the last 6 weeks of data.

To further clarify if a select the date 29/10/2023, then the visual will be updated for the period 24/09/2023 to 29/10/2023. If I select the date 10/09/2023, then the visual will be updated for the period 06/08/2023 to 10/09/2023

[Ritaf1983]

Hi @Haydn_R 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Haydn_R]

Sure, table with out any filters.

WeekEnding	Transfers
29/10/2023	484,933
22/10/2023	136,827
15/10/2023	72,677
8/10/2023	58,206
1/10/2023	254,763
24/09/2023	441,905
17/09/2023	116,789
10/09/2023	396,865
3/09/2023	156,845
27/08/2023	224,556
20/08/2023	437,243
13/08/2023	184,746
6/08/2023	176,791
30/07/2023	470,369
23/07/2023	199,365
16/07/2023	269,839
9/07/2023	239,575
Total	4,322,294

I then have a filter which will have WeekEnding and if I select 29/10/2023 then the table updates to the below:

WeekEnding	Transfers
29/10/2023	484,933
22/10/2023	136,827
15/10/2023	72,677
8/10/2023	58,206
1/10/2023	254,763
24/09/2023	441,905
Total	1,449,311

[Ritaf1983 · Accepted solution]

Hi @Haydn_R 
To achieve your goal you need to add a disconnected table with the weekend days.

For example with PQ :

1. create a reference to the fact table

2. remove all columns except the weekends :

3. close and apply and if there is a relationship automatically created, remove it:

4. use a wekkend from new table as a slicer

5. create a measure for the sum of transfers

transfers_ = sum('Table'[Transfers])

6. create a flag measure that will check if the dates of the fact table on visual are in the needed range

Flag =

 var _days  = 42

var _selected_date = MAX('weekdays'[WeekEnding])

return

IF( MAX('Table'[WeekEnding]) <= _selected_date && MAX('Table'[WeekEnding])>_selected_date -_days ,1,0)

7. put the data on the visual and use a flag as a filter :

A file with a solution is attached.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly