Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Current Year and previous year

Hi Community, 

 

I am having problem creating measure for below requirement. My data is like below. 

YearSales
2018400
20185
20194
20157
20164000
2014c
20163
201747
20199
20184
20186
20197
20155
20167
20149
20161
20172
20198
20183
201848
201969
201547
201665
2014

48

 

And I have a slicer on Year column. 

My requirement is to When ever the user click on a year it should should the the selected year value sales as Current year sales and previous year of the that selected year should be previuos year sales. Example like below.

 

If a user selects 2017 on the slicer then it should display 2017 sales as CY Sales and 2016 Sales as PY Sales. If a If a user selects 2018 on the slicer then it should display 2018 sales as CY Sales and 2017 Sales as PY Sales. 

 

Please help me.

 

Thank.

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    New a Year dimension table, and keep it to be unrelated to source data table. Add [Year] from this dimension table into slicer.

    Year dimension = VALUES('Yearly sales'[Year])

     

    Add [Year] from data table and below measures into table visual.

    CY sales =
IF (
    SELECTEDVALUE ( 'Yearly sales'[Year] )
        = SELECTEDVALUE ( 'Year dimension'[Year] ),
    CALCULATE (
        SUM ( 'Yearly sales'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Yearly sales' ),
            'Yearly sales'[Year] = SELECTEDVALUE ( 'Year dimension'[Year] )
        )
    )
)

PY sales =
IF (
    SELECTEDVALUE ( 'Yearly sales'[Year] )
        = SELECTEDVALUE ( 'Year dimension'[Year] ),
    CALCULATE (
        SUM ( 'Yearly sales'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Yearly sales' ),
            'Yearly sales'[Year]
                = SELECTEDVALUE ( 'Year dimension'[Year] ) - 1
        )
    )
)

    Best regards,

    Yuliana Gu

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous ,

     

    New a Year dimension table, and keep it to be unrelated to source data table. Add [Year] from this dimension table into slicer.

    Year dimension = VALUES('Yearly sales'[Year])

     

    Add [Year] from data table and below measures into table visual.

    CY sales =
IF (
    SELECTEDVALUE ( 'Yearly sales'[Year] )
        = SELECTEDVALUE ( 'Year dimension'[Year] ),
    CALCULATE (
        SUM ( 'Yearly sales'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Yearly sales' ),
            'Yearly sales'[Year] = SELECTEDVALUE ( 'Year dimension'[Year] )
        )
    )
)

PY sales =
IF (
    SELECTEDVALUE ( 'Yearly sales'[Year] )
        = SELECTEDVALUE ( 'Year dimension'[Year] ),
    CALCULATE (
        SUM ( 'Yearly sales'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Yearly sales' ),
            'Yearly sales'[Year]
                = SELECTEDVALUE ( 'Year dimension'[Year] ) - 1
        )
    )
)

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello v-yulgu-msft,

       

      Your formula is working but not giving any results. i would like to find YTD also for CY and PY based on selected year(i have a period column also). 

       

      Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Yuliana Gu,

           I'm also facing almost same situation as paruchuri447  mentioned below. But i need to filter values according to the slicer. suppose if i selecte 2019 as year from YEAR slicer, then i should get 2016,2017,2018 sales. this feature i implmented, but i couldn't achieve prioryear sales for all those years.

      sturcture should be as:

      category      2016             2017              2018

                          CY             CY      PY      CY      PY

      c1               100            200     100    300    200

      c2                20             50        20      80      50

      my matrix should look like this, 2018 py should get 2017 CY value. i tried with the process below, but i couldnot acheive the result. the PY looks blank for me always. can you provide me any idea for this?

       

      thanzz in advance..