topic Re: Display Current Selected year vs Previous year in DAX Commands and Tips

Display Current Selected year vs Previous year

Anonymous — Thu, 21 Apr 2022 07:14:05 GMT

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

Re: Display Current Selected year vs Previous year

amitchandak — Thu, 21 Apr 2022 08:00:02 GMT

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

Re: Display Current Selected year vs Previous year

Anonymous — Thu, 21 Apr 2022 23:43:09 GMT

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?

Re: Display Current Selected year vs Previous year

v-easonf-msft — Tue, 26 Apr 2022 06:44:08 GMT

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