The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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!
Proud to be a Super User!
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
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
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!
Proud to be a Super User!
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.
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.
Any ideas on what I'm missing here greatly appreciated.
Hi @veryconfused ,
please share your .pbix file, then I/we would be able to create the appropriate measure with your data and model.
Proud to be a Super User!
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!
Proud to be a Super User!
@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.