Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Display Current Selected year vs Previous year

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 

StreamYearDateMonthSubjectScore 
A20201/01/2020Maths60
A20201/02/2020Maths60
A20201/03/2020Maths60
A20201/04/2020Maths70
A20201/05/2020Maths60
A20201/06/2020Maths60
A20201/07/2020Maths60
A20201/08/2020Maths70
A20201/09/2020Maths70
A20201/10/2020Maths70
A20201/11/2020Maths50
A20201/12/2020Maths60
A20201/01/2020English80
A20201/02/2020English80
A20201/03/2020English80
A20201/04/2020English80
A20201/05/2020English80
A20201/06/2020English80
A20201/07/2020English80
A20201/08/2020English80
A20201/09/2020English80
A20201/10/2020English80
A20201/11/2020English80
A20201/12/2020English80
A20211/01/2021Maths40
A20211/02/2021Maths50
A20211/03/2021Maths90
A20211/04/2021Maths90
A20211/05/2021Maths90
A20211/06/2021Maths89
A20211/07/2021Maths78
A20211/08/2021Maths67
A20211/09/2021Maths90
A20211/10/2021Maths90
A20211/11/2021Maths90
A20211/12/2021Maths90
A20211/01/2021English40
A20211/02/2021English40
A20211/03/2021English40
A20211/04/2021English40
A20211/05/2021English60
A20211/06/2021English70
A20211/07/2021English70
A20211/08/2021English60
A20211/09/2021English60
A20211/10/2021English70
A20211/11/2021English70
A20211/12/2021English70
A20221/01/2022Maths80
A20221/02/2022Maths80
A20221/03/2022Maths80
A20221/04/2022Maths80
A20221/05/2022Maths80
A20221/06/2022Maths79
A20221/07/2022Maths85
A20221/08/2022Maths86
A20221/09/2022Maths88
A20221/10/2022Maths90
A20221/11/2022Maths90
A20221/12/2022Maths90
A20221/01/2022English90
A20221/02/2022English90
A20221/03/2022English90
A20221/04/2022English90
A20221/05/2022English90
A20221/06/2022English90
A20221/07/2022English90
A20221/08/2022English90
A20221/09/2022English85
A20221/10/2022English85
A20221/11/2022English84
A20221/12/2022English94



  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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