cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
amkumar5 Member
Member

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. 

 

Trend.PNG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-joesh-msft Established Member
Established Member

Re: Dynamic selection and filtering on Chart

Hi @amkumar5 ,

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:

11.PNG12.PNG

 

 

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETGKc3g6UTFHl27NzE...

 

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.

View solution in original post

4 REPLIES 4
v-joesh-msft Established Member
Established Member

Re: Dynamic selection and filtering on Chart

Hi @amkumar5 ,

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:

11.PNG12.PNG

 

 

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ETGKc3g6UTFHl27NzE...

 

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.

View solution in original post

amkumar5 Member
Member

Re: Dynamic selection and filtering on Chart

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

Re: Dynamic selection and filtering on Chart

Hi @amkumar5 ,

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.

amkumar5 Member
Member

Re: Dynamic selection and filtering on Chart

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!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 449 members 4,093 guests
Please welcome our newest community members: