Dynamic selection and filtering on Chart

[Anonymous]

Hi, I've a requirement to show last 3 years data based on user selection.

So, If a user selects 'Reporting Period' as 2019.Q2 then i want values of 2019.Q2, 2018.Q2, 2017.Q2.

Last 3 years data for a selected Quarter (in this case Quarter 2 is selected).

I've created table using SUMMERIZE, it contain Sales by each Quarter every year. In this table Sales, Year, Quarter, Reporting Period.

By default it is displaying all the Quarters. 

I tried by creating additional column in this SUMMERIZE table to see

IF(SalesAgg[Quarter] = SELECTEDVALUE(Dates[Quarter]),"Same Quarter", "All Quarters")

and

then I applied Visual level filter to to include only "Same Quarter" but it's not working. because it seems we cannot use SELECTEDVALUE in Column defintion.

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

Based on my research, I think you should create a new unconnected table and create the following measure as below:

1. You need a separate date table for the slicer to choose year- quarter(do not create any relationship with other tables);

2. Create a measure like the following:

Measure =
IF (
    ISFILTERED ( 'Dim selected table'[YQ] ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Date',
            AND (
                'Date'[Year]
                    = SELECTEDVALUE ( 'Dim selected table'[Year] ) - 1
                    || 'Date'[Year]
                        = SELECTEDVALUE ( 'Dim selected table'[Year] ) - 2
                    || 'Date'[Year] = SELECTEDVALUE ( 'Dim selected table'[Year] ),
                'Date'[Quarter] = SELECTEDVALUE ( 'Dim selected table'[Quarter] )
            )
        )
    ),
    CALCULATE ( SUM ( 'Table'[Value] ) )
)

Results are as follows:

Here is a demo, please try it：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETGKc3g6UTFHl27NzE0aCrEBhOvPgwsCVHVQRmd5bKdOxQ?e=2lX5i8

Best Regards,

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

[Anonymous]

Thank you for looking into this question and providing solution, Joey.

Problem is my entire report is dependent on this Year/Quarter slicer/DropDown. There are 2 another slicers, Table visual, chart and also 20 measures that are already created.

If I create separate table to use year/Quarter from that table, then all other Visuals will be out of sync and needs to be updated.

I havent tried your solution yet as am unable to open attached file. In our office we are using older version (PBIRS Jan 19) and attached file is latest version.

[v-joesh-msft]

Hi @Anonymous ,

I think if you want to achieve the results you want, a separate table may be necessary.

I suggest you download the latest version of the power bi desktop to view attachments.

Here is a post similar to your case, you can also refer to the following, hope to be helpful:  

I want to display last 5 years sales when i select one year

Best Regards,

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

[Anonymous]

Am able to convince users to have separate report/page (no sync from other pages) for this 3-Year analysis as this report does not require any other Slicers/Filters. Requirement is to display Total Sales only by Year not Product, Location.

IF(ISFILTERED('Dim selected table'[YQ]),

                          CALCULATE(SUM('Table'[SaleAmount]),

                                                          FILTER('Date',AND('Date'[Year] > SELECTEDVALUE('Dim selected table'[Year])-3 ,

                                                                     'Date'[Quarter]=SELECTEDVALUE('Dim selected table'[Quarter]))))

,CALCULATE(SUM('Table'[SaleAmount])))

Thank you Joey!