Slicer Filter Interaction With Chart

[veryconfused]

I have a weekly report that needs to show data from that week, controlled by a slicer set as a dropdown (ex, Week Ending 8/9).

The issue is that I need all visualizations to follow the slicer except I would like a chart showing the last 8 weeks of sales. I can remove interaction between chart and slicer - the problem is, if the end consumer changes the slicer (ex, they want to look at Week Ending 7/26), I want the chart to show the last 8 weeks of sales from 7/26 backwards. With interaction removed, this doesn't happen, the chart is static.

I suppose I want the slicer to be a filter on most of the report, but only drive the end date of the chart.

Is this possible? I'm trying to avoid having a second slicer for the chart, with the user having to remember to update two slicers.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[veryconfused]

Hi @richbenmintz 

Had to whip up some dummy data. Posted at the link below. Thanks again.

(Edit: Below link updated w/ data showing different sales per week.)

https://www.dropbox.com/s/lxfoxejuh43kzmt/DummyReportSlicers.pbix?dl=0 

[richbenmintz · Accepted solution]

Hi @veryconfused ,

please try this measure, it will show the dynamic weeks on the x axis, as an aside your data seems to be the same for each week, why all the bars are the same height.

z.Dynamic X Axis = 
    VAR SelectedWeek = max(fSales[Week End Date (Saturday)])
    VAR FloorWeek = max(fSales[Week End Date (Saturday)]) -21
RETURN
    IF(
        HASONEVALUE(Weeks[Week End Date (Saturday)]) && 
        VALUES(Weeks[Week End Date (Saturday)]) >= FloorWeek &&
        VALUES(Weeks[Week End Date (Saturday)]) <= SelectedWeek,
            CALCULATE(sum(fSales[TY Net Sales]),
               fSales[Week End Date (Saturday)] =  values(Weeks[Week End Date (Saturday)])),
            BLANK()
    )

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[veryconfused]

@richbenmintz 

Fantastic, thank you! Seems I had reversed the disconnected and connected date columns in your measure formula.

The sales each week were the same on the dummy data (put it together too fast) - I updated the link above for a version with varying sales data per week in case this thread helps anyone else in the future.

[veryconfused]

Thanks to all for posting replies, and links to forums resources. I have been working on this for hours and can't quite figure the solution..

@Greg_Deckler  and @richbenmintz :

I have tried both of these solutions with the same outcome.

• Created a disconnected dates table for both solutions, with a single column of the date.
• The slicer is using the date from my sales table, not the disconnected date - tried using the disconnected date for the slicer and got no results
• In both cases, the chart now shows the desired weeks back on the x axis
• However, the value measure I'm trying to calculate (sum of sales) shows the total for all 3 weeks in each week, rather than each week's total.
  • I feel like this must have something to do with using the disconnected date table and I can't quite understand how to square it.
  • To note, in either case I'm calling a measure I created earlier that's simply SUM(Net Sales). I also tried to write it SUMX(SalesTable, Net Sales) to no change.

Any ideas on what I'm missing here greatly appreciated.

[richbenmintz]

Hi @veryconfused ,

please share your .pbix file, then I/we would be able to create the appropriate measure with your data and model.

[richbenmintz]

Hi @veryconfused,

You will need a disconnected Week Dimension that you can use as the X Axis for your Chart. Then you would create a measure like the one below: 

Dynamic X axis = 
    var selectedWeek = max('calendar'[Week])
    var floorWeek = max('calendar'[Week])-6
return
    if
        (
            HASONEVALUE('Calendar dynamic'[Week]) && VALUES('Calendar dynamic'[Week]) >=floorWeek && VALUES('Calendar dynamic'[Week]) <= selectedWeek, 
            CALCULATE(SUM(FTE[FTE]), 'Calendar'[Week] = VALUES('Calendar dynamic'[Week]))
            ,BLANK()
        )

Essentially you are assigning a value where the disconnected week is between the floor and ceiling weeks, otherwise Blank() is returned, leaving you with a chart that looks something like

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Greg_Deckler]

@veryconfused - You want The Complex Selector. The example solved is almost exactly your scenario:

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Also, since I see you are a New Member, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.