cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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

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.

Capture.JPG

 

9 REPLIES 9
Helper II
Helper II

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.

Community Support
Community Support

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
)

control.png

 

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.

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?

Capture1.JPG

 

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

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.

 

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

Capture1.JPG

Capture.JPG

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.

Hi, below is a link to the file.

 

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

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.

 

Super User IV
Super User IV

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors