Show only last 4 weeks

[Quiny_Harl]

Hello!

I have a visual which has to show only last 4 weeks. In my date table I have a week column.

However, when I apply relative date filter on the visual, the results are wrong. I guess because the way weeks are counted in my date table and the way Power BI count weeks are different, idk.
Another problem is that, for example, week 1 should appear last (this is week 1 of 2022) followed by week 53 and so on. 

[Quiny_Harl · Accepted solution]

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.

[v-yalanwu-msft]

Hi, @Quiny_Harl ;

1.So you could create a mesure.

last n week = 
CALCULATE(DISTINCTCOUNT('Table'[weeknum]),FILTER(ALL('Table'),WEEKNUM([date],21)<>WEEKNUM(TODAY(),21)&&[date]>=MIN('Table'[date])))

Then you could apply it into filter .

2.As sort problem, you could create another sort column.

a)create a column

sort = WEEKNUM( [date]- DATEDIFF( DATE(YEAR(MAX('Table'[date]))-1,12,31)-1,MAX('Table'[date]),DAY),21)

b)sort by sort column.

c)set sort .

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Quiny_Harl]

@v-yalanwu-msft  Thank you for the suggested solution! It seems like it would work. Yet, I accepted my reply as a solution since it seems to me that it is more straightforward and easy to implement.

[GyanendraMaurya]

Hi @Quiny_Harl,

First please check you need last 3 weeks or 3 Calender weeks of data in viz.

If you select Calenderweeks the complete calendar week is always considered for the period. This means it will start on Saturday and end on Sunday.

If you select Weeks, it just goes back seven days. If the relative date is the last 3 weeks, it just goes back 21 days from the current day.

For Sorting: you can sort this visual by Date Earlist Date which would fix it. Another workaround it to create and use table, where make weeknum as text value and sort them by date.

Now if instead of showing data for last weeks you can do some customization and make it like last 30,60,90 days.

For more help and reference please visit:

https://youtu.be/h6f_WKkf6kQ

If it helps and solve the issue, Please accept it as answer and subscribe to my channel. 

Thank you!

[jdbuchanan71]

@Quiny_Harl 

Last 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_Harl]

Hi @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.