i have a table with years/quarters and total sales like
2019 4 111
2018 4 123
2017 4 145
2016 4 166
2015 4 178
on page 1 i have the slicer YEAR = 2019 and QUARTER = 4. how can i choose the total sales for the last year (YEAR -5) and show it for every year like
2019 4 178
2018 4 178
2017 4 178
2016 4 178
maybe there are some simple ways?
Go to Solution.
Table 2 = SUMMARIZE('Table','Table'[year],'Table'[quarter])
Create a measure
MAX ( 'Table'[year] )
>= SELECTEDVALUE ( 'Table 2'[year] ) - 4
&& MAX ( 'Table'[year] ) <= SELECTEDVALUE ( 'Table 2'[year] )
&& MAX ( 'Table'[quarter] ) = SELECTEDVALUE ( 'Table 2'[quarter] ),
SUM ( 'Table'[value] ),
ALL ( 'Table' ),
= SELECTEDVALUE ( 'Table 2'[year] ) - 4
&& 'Table'[quarter] = SELECTEDVALUE ( 'Table 2'[quarter] )
Community Support Team _ Maggie LiIf 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
1. select years 2015 to 2019 and Quarter 4 in the slicers,
2. place Year and Quarter in a table visual
3. Create this measure and place it in the visual
Measure = CALCULATE(SUM(Table1[Sales]), Table1[Year] = 2015)
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
i have only a single slicer YEAR = 2019 and QUARTER = 4
1) the table should show the last 5 years with the columns YEAR/QUARTER/SALES
2) and i need an another column with the sales of the last year (in example 2015) for every row
i need a dynamic solution on one page without to create a new table because i work in a live connection (not possible to create a table).
Check out the on demand sessions that are available now!
Check out the Winners!
Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.