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.
Community SupportHi, 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.
Advocate IIIv-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.