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

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.

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

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

        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!