Forum Discussion
update visual for previous 6 weeks when a date is selected.
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
Hi Anonymous
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 rangeFlag =var _days = 42var _selected_date = MAX('weekdays'[WeekEnding])returnIF( 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
3 Replies
- Ritaf1983
Super User
Hi Anonymous
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/1447523Sure, 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
Hi Anonymous
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 rangeFlag =var _days = 42var _selected_date = MAX('weekdays'[WeekEnding])returnIF( 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