How to add selected value reference line to line chart when interaction with slicer is turned off

[ZLV]

Hello,

I would like to add an x-axis reference line to my line chart. The reference line uses a measure that takes the selected week minus the selected weeks back. The measure returns the desired value in a card but when I select the measure for the reference line it returns the wrong value. This is because I have interaction with the fiscal week slicer turned off for the line chart. If I turn this on then the line chart only graphs the selected week and hides all others, making it useless.

Is there a work around here?

[v-chenwuz-msft]

Hi @ZLV ,

There is no way to get the value of the slicer when you turn off the interaction. So let the value of line chart has no interaction with the slicer. 

So, please create a new table named "fiscal week(2)" via VALUES('date'[Fiscal week]) and use the 'fiscal week(2)'[Fiscal week] for the slicer. Do not forget to modify the "R_Week Reference Line" to get the values from the new slicer.

If you need more help, please share your pbix file without sensitive data and expect output.

Best Regards

Community Support Team _ chenwu zhu

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

[ZLV]

Thanks for your reply. My data (and the x-axis) is all weekly so I don't have a date field. 

[amitchandak]

@ZLV , with hlep from week rank on week/date table

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

you can get measure

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

or

behind by selected weeks

Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-[Selected week back]))

diff of above can be used as line or as constant line using analytics pane

diff= [This week] - [Last week]

or

diff= calculate([This week] - [Last week], allselected())

Power BI — Week on Week and WTD
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...
https://www.youtube.com/watch?v=pnAesWxYgJ8

In case you want trend till a week back

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI