Slicer to show only the last 18 Fiscal weeks

[Anonymous]

Hi,

I need the slicer to show the last 18 fiscal weeks including the current week. Can some one help?

Adding the date to the filter pane did not help, I've tried to set to the last 18 weeks, the starting week is not a full week, it will filter from 21/02/2020 til today,  I want to filter from 16/02/2002. The other option is  the last 18 calaneder week exculdes current week.

Thanks in advance.

[amitchandak]

@Anonymous , Create a date calendar. And restrict you slicer using relative date slicer at page .

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

for week refer

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Anonymous]

@amitchandak , I have done that as well as I mentioned in my first post, that won't work. 

"Adding the date to the filter pane did not help, I've tried to set to the last 18 weeks, the starting week is not a full week, it will filter from 21/02/2020 til today,  I want to filter from 16/02/2002. The other option is  the last 18 calaneder week exculdes current week."

[Anonymous]

As you can see from below screenshots, the filter type Relative date for 'weeks' and 'Calendar weeks'. You can see the date range for 'weeks' starts from 21/02/2020, which is Thursday of that week, it should be 16th of 17th /02/2020.

As for 'calendar weeks' it exclude current week because of it is not a complete week.

[Anonymous]

Hi  @Anonymous ,

Create a calculated column as below:

Column = 
var _mindate=CALCULATE(MIN('Table'[Date]),FILTER('Table',YEAR('Table'[Date])=YEAR(TODAY()-7*18)&&WEEKNUM('Table'[Date],1)=WEEKNUM(TODAY()-7*18,1)))
Return
IF('Table'[Date]>=_mindate&&'Table'[Date]<=TODAY(),'Table'[Date],BLANK())

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@Anonymous , thank you, that helped.

[harshnathani]

Hi @Anonymous ,

You can try this trick for weeks too

http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-power-bi.html

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Thanks @harshnathani , this is not what I want. my issue is related to the just the fiscal week slicer not a specific measure. I want the user to see only within the last 18 fiscal weeks in the slicer.

BTW, my data source is tabular medel.