Line chart to show 3 lines based on quarter selection

[Anonymous]

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 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

[v-lili6-msft]

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

[Anonymous]

Hi v-lili6-msft,

Actually my requirement is to have a line chart as below, when I select 2019 Q2 I should get three lines for 2019 Q2, 2018 Q2 and 2019 Q1. The X axis is WeekOfQuarter derived by 

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

where 

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.

[v-lili6-msft]

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