Date filter to last 6 fiscal weeks, for 2019 and 2020 fiscal weeks

[romoguy15]

Hello,

I have a simple report that contains HVAC repair data from 2019 and 2020. I would like to create a formula that has a rolling 6 weeks filter for the data to show 2019 and 2020 within the last 6 weeks in our accounting calendar. My report does have an accounting calendar to refer to and my data does have relationship to the calendar.

Anyway, if I try relative date filter and do last 6 weeks, my 2019 data goes blank.  Our fiscal weeks in our accounting calendar are the same every year. So my data needs to show 2019 and 2020 data last 6 weeks of the current accounting week. Right now, we are in week 27 of the accounting calendar. I want the data to show fiscal weeks 21 through 26 both for 2019 and 2020. Then when we are in week 28, I want the report automatically shows data for fiscal weeks 22 through 27, again both for 2019 and 2020.

Right now, every week I have to go to the filter pane and change the basic filter for fiscal weeks manually in order for me to have 2019 and 2020 show. Actual dates don't matter because if I used date going back to 6 weeks, 2019 would be blank. Fiscal weeks is what we go by.

[romoguy15]

So as a refresher, I need the data to be constatly refreshed to show this year and less year data from the last 6 fiscal weeks.

Example, if today is 12-8-20, that means we are in fiscal week 45. So the chart would show data between 39-44. But the tricky part is showing it to match the 2019 data 39-44 fiscal weeks.

[v-yingjl]

Hi @romoguy15 ,

You can create a control measure like this, put it in the visual filter and set its value as 1:

Control = 
var _week = SELECTEDVALUE('Date'[Week])  - use a slicer to define week
// var _week = WEEKNUM(TODAY(),2)     - define current week
return
IF(
    SELECTEDVALUE('Table'[Fisical Week]) <= _week -1 &&
    SELECTEDVALUE('Table'[Fisical Week]) >= _week -6,
    1,0
)

Attached a sample file that hopes to help you: week filters.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[romoguy15]

Hello Yingjie Li,

I have typed out the eact formula you've written below and I got a sintax error that says the syntax for 'use' is incorrect. Did I miss a character in the formula?

Also, I need this measure to filter on all pages or the current page and it will not allow me to do that. 

[v-yingjl]

Hi @romoguy15 ,

"- use a slicer to define week" is also a notation so it should be added with "//" in front of it

var week = ........     // -use a slicer to define week

In addition, measure can only be used in visual filters, page filters cannot support quote measure. If you want to apply it on all pages or current page, you need to put it in all visual filters.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[romoguy15]

Okay so I typed the formula as written and added it to the visual level filter and it makes the line chart go blank

[v-yingjl]

Hi @romoguy15 ,

This formula is used based on a slicer to define 'current week'. If there is no slicers in your report, it will show blank when using this formula.

Maybe you can consider sharing a dummy .pbix file for further discussion. Remeber to replace the sensitive information in the file.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[romoguy15]

Hi, below is a link to the file.

https://1drv.ms/u/s!AqID1H0nHPOzg3lYgiPfX2F7icLg

[romoguy15]

Hi v-yingjl,

I sorry for not replying back soon. I will work on create a dummy file so I can share to make this easier and hopefully come up with a way to make this work.

[amitchandak]

@romoguy15 , you have try something like

https://www.youtube.com/watch?v=duMSovyosXE

also refer

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...