cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Line chart to show 3 lines based on quarter selection

Hi Teams,

 

I want to plot one line chart to show 3 lines on a single selection of quarter from the Date Hierarchy slicer, one line should be for current year quarter (selected quarter), another for previous year same quarter and last one for Previous quarter of the selected quarter.

For eg: An user selects Q1 2019, then the 3 lines would be for Q1-2019, Q1-2018 and Q4-2018.

 

On my report page there is a Date Hierarchy slicer as below
Date Hierarchy SlicerDate Hierarchy Slicer 

I have written the following measure to calculate  the cummulative sum of revenue as below based on WeekOf Qaurter.

CALCULATE(
    SUM('Revenue'[Revenue]),
    FILTER(
        ALLSELECTED('Revenue'[WeekOfQuarter]),
        ISONORAFTER('Revenue'[WeekOfQuarter], MAX('Revenue'[WeekOfQuarter]), DESC)
    && MAX(Revenue[INVOICE_QUARTER]) = [SELECTED QUARTER]
&& MAX(Revenue[INVOICE_YEAR]) = [SELECTED YEAR]
)
)
 
The Other measures are 
PY Quarter = 
CALCULATE(
    SUM('Revenue'[Revenue]),
    FILTER(
        ALLSELECTED('Revenue'[WeekOfQuarter]),
        ISONORAFTER('Revenue'[WeekOfQuarter], MAX('Revenue'[WeekOfQuarter]), DESC)
    && MAX(Revenue[INVOICE_QUARTER]) = [SELECTED QUARTER]
&& MAX(Revenue[INVOICE_YEAR]) = [SELECTED YEAR]-1
)
)
 
But due to slicer connection the graph only shows 1 line and filters out the others. I dont want the other 2 lines to get filtered on selection.
How can we achieve this?
 
Thanks,
Anish

 

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Line chart to show 3 lines based on quarter selection

hi, @Anonymous 

As you said, "due to slicer connection the graph only shows 1 line and filters out the others. I dont want the other 2 lines to get filtered on selection." when a user selects Q1 2019, the visual will only show Q1 2019 dim period.

Q1-2018 and Q4-2018 only will appear in Q1 2019 dim period with another line.

 

There is another to get it is that use an independent date table for slicer.

 [SELECTED QUARTER] and [SELECTED YEAR] is based on this independent date table.

here is a similar post for you refer to:

https://community.powerbi.com/t5/Desktop/Filter-of-some-period-and-get-the-information-from-the-previous/m-p/326441#M145653

 

If you still have the problem, please share your sample pbix file and expected output.

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

Re: Line chart to show 3 lines based on quarter selection

Hi 

WeekOfQuarter = ROUNDUP(MOD(Revenue[WeekNum],MAX([WeekNum])/4),0)

WeekNum = WEEKNUM(Revenue[Line GL Date],2).
The lines as showing the cummulative Revenues for those respective quarters.
RevenueasperSelection = CALCULATE(
    SUM('Revenue'[Revenue]),
    FILTER(
        ALLSELECTED('Revenue'[WeekOfQuarter]),
        ISONORAFTER('Revenue'[WeekOfQuarter], MAX('Revenue'[WeekOfQuarter]), DESC)
    )
)

So when I select other quarters the line must change accordingly which is not working for me.

Highlighted
Community Support
Community Support

Re: Line chart to show 3 lines based on quarter selection

hi, @Anonymous 

Could you share your sample pbix file for us have a test?

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading

 

Best Regards,

Lin

 

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors