Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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:
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.
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:
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.
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.
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.
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.
Thank you Joey!