chose a certain value and show it for every row

[powerbityro]

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?

[v-juanli-msft · Accepted 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.

[AlB]

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  

[powerbityro]

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