Complex Filter Scenario - Help?

[granthworth]

Hey guys/gals,

I have created a weekly report with line graph visuals showing trends for the last 6 weeks for several categories. I also have a bar graph visual and radial gauge/overlayed KPI visual that shows data for the most recent week in the database broken out by day. I am using 2 visual level filters to accomplish this:

For the trend line visual:

RecentWeeks = if(and('Report Card'[Week_rank] >= 1,'Report Card'[Week_rank] <= 6),"Yes","No")

For the most recent week visuals:

Week_current = if('Report Card'[Week_rank] = 1,"Yes","No")

....where Week_rank = rankx(all('Report Card'[Year+Week]),'Report Card'[Year+Week])

Now, herein lies the problem. In the above screenshot, the first data entry for the week has been made and thus, the report treats week 1647 as the most recent week and therefore automatically tailors all of the visuals for displaying the detailed (daily) data for this week. What if the user wants to see the details for last week? There is no easy or feasible way for them to select a different week for "Week_current" or a different set of 6 weeks for "RecentWeeks". Is there any solution to this that I may be overlooking?

Thanks for the input!

[Greg_Deckler]

Perhaps this nifty techniqu could help by making your measures or other calculations dynamic based upon slicers?

http://community.powerbi.com/t5/Community-Blog/Dynamically-change-the-information-within-a-visual-vi...

[granthworth]

Ok, I have made a little progress. I am still running into some confusion though. Hopefully the explanation below pinpoints my issue....

So, I created a table with 2 columns which pulls in all of the distinct values for "Year+Week" from my main data table and then ranks them from 1 to n based on a rankx formula. This should serve the same purpose as the "Measure Dimensions' table in the article linked by @Greg_Deckler

So, in the ideal scenario, the slicer would display all values from the [Distinct_Year+Week] column and allow the user to select which week to show the report details for. However, and this is the difference between my situation and the linked article, this list of [Distinct_Year+Week] options will continue to grow as time progresses whereas the author of the article was only switching between 3 fixed options (Sales, Costs, and Profits). Therefore, my SWITCH formula woul dhave to be a massively nested pile of code in order to account for the hundreds of week values both in the past and future data.

This is where I am currently stuck. 

[granthworth]

Hey guys,

I have implemented 2 slicers in the meantime (see below) to allow our users to view data from other weeks but I am still VERY interested in learning from other users for more user-friendly solutions.

The report has been saved with "Yes" selected as the default setting in the "Current Week" slicer. So this slicer looks for the MAX number for Year+Week. If a user needs to view another, previous week, this slicer should be unselected and the slicer for "Year+Week" would be selected with the week in question. 

Default setting for most recent (or current) week available

User has selected a different week here

Ideally, the left side of the report (showing previous 6 weeks history) would be automatically updated based on the user slicer selections above and would show the previous 6 weeks prior to whatever week is selected. However, under this scenario, it remains static, showing the previous 6 weeks as calculated from the MAX Year+Week column.

I hope this makes sense! Any other information is GREATLY appreciated!

[GilbertQ]

Hi @granthworth why not just use a Period Table, and then you can define any period that you like. In doing so, you can then create a slicer for the past 6 weeks, and what ever combination you need?

http://community.powerbi.com/t5/Data-Stories-Gallery/Create-Dynamic-Periods-for-Fiscal-or-Calendar-D... In there you will find all the details on how to easily create your measures for what it appears you are after?

[granthworth]

I will definitely be trying this out tomorrow. Thank you very much for the link! I will report back with my findings.