Forum Discussion
Advocate IIIShow only last 4 weeks
The easiest solution I found:
1. Go to Power Query, in your Date table, create a custom column that flags the last 4 weeks:The column is going to return TRUE for all rows that fall in the last 4 weeks, as of current date, and FALSE for the rest
2. Put the Last 4 Weeks Flag measure as a filter on your visual and select true.
This will show only the last 4 weeks of your Date table.
Important note: Currently Power BI has a bug where when you upload the data to Power BI Service the TRUE/FALSE values get converted to -1/0. This will mess up your visual. One option wpuld be to open the report in Service and select -1 in the visual filter since it correcponds to TRUE. Another option would be to change your true/false column into a text column.
Now for the correct sorting:
1. Go to Power Query, in your Date table, create a custom column that concatenates the Year and the Week column. In my case, this would look like this:
2. Then create the following measure:
Sort Week Measure = MIN('Date'[Year Week])3. Since my visual where I want to show the last 4 weeks is of a type line and column chart, I put Sort Week Measure as a line value and make it invisible by using the formatting pane - shapes => customize series => select your sorting measure =>set stroke width to zero, then turn off the data labels.4. Then I sort my visual by the Sort Week Measure in ascending order.
Super UserLast 3 weeks and Last 3 calendar weeks are 2 different date ranges. Calendar weeks return only completed weeks meaning it stops on Sat.
To get the weeks in the right order you will have to add the year as part of the axis. PowerBI has no idea that week 1 is week 1 of 2021 unless the year field is in the visual.
Quiny_HarlHi jdbuchanan71 . This is not working neither with week filter, nor with calendar week filter - those filters just give wrong results. Also, I can't add year as part of the axis. The solution is obviously not gonna be using the default relative date, it has to be some workaround.