Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi all,
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
2015 4 178
maybe there are some simple ways?
Solved! Go to Solution.
Hi @powerbityro
Create table2
Table 2 = SUMMARIZE('Table','Table'[year],'Table'[quarter])
Create a measure
Measure = IF ( MAX ( 'Table'[year] ) >= SELECTEDVALUE ( 'Table 2'[year] ) - 4 && MAX ( 'Table'[year] ) <= SELECTEDVALUE ( 'Table 2'[year] ) && MAX ( 'Table'[quarter] ) = SELECTEDVALUE ( 'Table 2'[quarter] ), CALCULATE ( SUM ( 'Table'[value] ), FILTER ( ALL ( 'Table' ), 'Table'[year] = SELECTEDVALUE ( 'Table 2'[year] ) - 4 && 'Table'[quarter] = SELECTEDVALUE ( 'Table 2'[quarter] ) ) ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @powerbityro
Create table2
Table 2 = SUMMARIZE('Table','Table'[year],'Table'[quarter])
Create a measure
Measure = IF ( MAX ( 'Table'[year] ) >= SELECTEDVALUE ( 'Table 2'[year] ) - 4 && MAX ( 'Table'[year] ) <= SELECTEDVALUE ( 'Table 2'[year] ) && MAX ( 'Table'[quarter] ) = SELECTEDVALUE ( 'Table 2'[quarter] ), CALCULATE ( SUM ( 'Table'[value] ), FILTER ( ALL ( 'Table' ), 'Table'[year] = SELECTEDVALUE ( 'Table 2'[year] ) - 4 && 'Table'[quarter] = SELECTEDVALUE ( 'Table 2'[quarter] ) ) ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @powerbityro
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.
Cheers
Hi AlB,
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).
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |