Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic selection and filtering on Chart

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.

Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    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.

4 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.