Display Current Selected year vs Previous year

[Anonymous]

I have data similar to the one below

I would like to use a year slicer to control a stacked Column chart. I have one stream that has data from 2020 - 2022 with scores for two subjects Maths & English. Ideally when i select Year 2021 on my slicer I would like to see only the current year average scores and the previous year shown for example, select 2021 see only A2021 and A2020.

A is just a stream/Class concatenated with the Year. 

Here is the data 

StreamYear	DateMonth	Subject	Score
A2020	1/01/2020	Maths	60
A2020	1/02/2020	Maths	60
A2020	1/03/2020	Maths	60
A2020	1/04/2020	Maths	70
A2020	1/05/2020	Maths	60
A2020	1/06/2020	Maths	60
A2020	1/07/2020	Maths	60
A2020	1/08/2020	Maths	70
A2020	1/09/2020	Maths	70
A2020	1/10/2020	Maths	70
A2020	1/11/2020	Maths	50
A2020	1/12/2020	Maths	60
A2020	1/01/2020	English	80
A2020	1/02/2020	English	80
A2020	1/03/2020	English	80
A2020	1/04/2020	English	80
A2020	1/05/2020	English	80
A2020	1/06/2020	English	80
A2020	1/07/2020	English	80
A2020	1/08/2020	English	80
A2020	1/09/2020	English	80
A2020	1/10/2020	English	80
A2020	1/11/2020	English	80
A2020	1/12/2020	English	80
A2021	1/01/2021	Maths	40
A2021	1/02/2021	Maths	50
A2021	1/03/2021	Maths	90
A2021	1/04/2021	Maths	90
A2021	1/05/2021	Maths	90
A2021	1/06/2021	Maths	89
A2021	1/07/2021	Maths	78
A2021	1/08/2021	Maths	67
A2021	1/09/2021	Maths	90
A2021	1/10/2021	Maths	90
A2021	1/11/2021	Maths	90
A2021	1/12/2021	Maths	90
A2021	1/01/2021	English	40
A2021	1/02/2021	English	40
A2021	1/03/2021	English	40
A2021	1/04/2021	English	40
A2021	1/05/2021	English	60
A2021	1/06/2021	English	70
A2021	1/07/2021	English	70
A2021	1/08/2021	English	60
A2021	1/09/2021	English	60
A2021	1/10/2021	English	70
A2021	1/11/2021	English	70
A2021	1/12/2021	English	70
A2022	1/01/2022	Maths	80
A2022	1/02/2022	Maths	80
A2022	1/03/2022	Maths	80
A2022	1/04/2022	Maths	80
A2022	1/05/2022	Maths	80
A2022	1/06/2022	Maths	79
A2022	1/07/2022	Maths	85
A2022	1/08/2022	Maths	86
A2022	1/09/2022	Maths	88
A2022	1/10/2022	Maths	90
A2022	1/11/2022	Maths	90
A2022	1/12/2022	Maths	90
A2022	1/01/2022	English	90
A2022	1/02/2022	English	90
A2022	1/03/2022	English	90
A2022	1/04/2022	English	90
A2022	1/05/2022	English	90
A2022	1/06/2022	English	90
A2022	1/07/2022	English	90
A2022	1/08/2022	English	90
A2022	1/09/2022	English	85
A2022	1/10/2022	English	85
A2022	1/11/2022	English	84
A2022	1/12/2022	English	94

[Anonymous]

@amitchandak the above didn't work.

Could the summarize function help with this?

Something like

Selected Year Score =

SWITCH( SELECTEDVALUE(Date[Year]),

"2021",SUMMARIZE(
           'Data',Data[DateMonth],
           "Score2021", Calculate(SUM('Data'[Score]),Filter('Data','Data'[StreamYear] = "A2021")),
           "Score2020",Calculate(SUM('Data'[Score]),Filter('Data','Data'[StreamYear] = "A2020"))))
Though I'm getting an error"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

Would this work?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Try steps as below:

1.create/enter a seperate table 'Slicer' as below:

Slicer = VALUES('Date'[Year])

2. add a measure as below and apply it to visual filter pane

filter control = 
VAR a =
    IF (
        MAX ( 'Date'[Year] ) = MAX ( Slicer[Year] )
            || MAX ( 'Date'[Year] )
                = MAX ( Slicer[Year] ) - 1,
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( Slicer[Year] ), a, 1 )

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , Please create rank of your AY

a new column

Year Rank = RANKX(all('Date'),'Date'[AY],,ASC,Dense)

Then you can measure like these
This Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))