Create field relative to year

[RyanMarshall82]

I have a dataset that compares values in a current year to other years At the moment, I have an IF statement as a column, that essentially just says: 

IF data.year = '2023' then "CurrentYear"

IF data.year = '2023'-1' then "PriorYear"

IF data.year = '2023'-2 then "Y-2" etc...

So for my 'data' table, each row has a categorisation as a string e.g. "CurrentYear", "PriorYear" etc. I then use this to do measures for each value I need using SUMX and IF. 

This works fine for my purposes, but as we approach a new year, I want to be able to swap '2023' hard coded with a slicer or similar, so I can quickly change the report to use 2024 as a base year. 

I created a new table with the available years, and setup a slicer to select only one. I can refer to this using 'SELECTEDVALUE'. However, these can only be referred to in measures, not columns. 

How would we build something that essentially does this: 

IF data.year = '[SELECTEDVALUE]' then "CurrentYear"

IF data.year = '[SELECTEDVALUE]'-1' then "PriorYear"

IF data.year = '[SELECTEDVALUE]'-2 then "Y-2" etc...

Apologies can't attach dataset as it is huge and confidential, but hopefully that makes sense? Thanks for any help, first time poster! 

[RyanMarshall82]

Thanks for the help, to confirm I want to specify the selectedyear, not use current year necessarily. 

So I can confirm that the formula provided by @Arpitb12 works if I use max instead of SelectedValue, so it must be a problem with that. Ie, this works as expected: 

Category = VAR SelectedYear =

        max ( CY[Year] )

    RETURN SWITCH( TRUE(), Sales[Year] = SelectedYear, "CurrentYear", Sales[Year] = SelectedYear - 1, "PriorYear", Sales[Year] = SelectedYear - 2, "Y-2", "Other" )

Min, or Max both deliver results as I'd expect, but I just can't get the selectedvalue to work!

[Uzi2019]

Hi @RyanMarshall82 

if my post helps please accept it as a solution so other may find it helpful.

[RyanMarshall82]

Edit: This feels so close, but it's still not quite working for me. I've got two tables, the years for slicer are in 'CY' and the data is in 'Sales'. The formula now reads (as a column): 

Category = VAR SelectedYear = IF (

        HASONEVALUE ( CY[Year] ),

        SELECTEDVALUE ( CY[Year] ),

        BLANK ()

    ) RETURN SWITCH( TRUE(), Sales[Year] = SelectedYear, "CurrentYear", Sales[Year] = SelectedYear - 1, "PriorYear", Sales[Year] = SelectedYear - 2, "Y-2", "Other" )

At present, everything is returning as "Other", even on a sheet where I can see the SelectedYear is changing to one of the years in Sales[Year]

[Uzi2019]

Hi @RyanMarshall82 
First create calculated column for Year like this 

Year_ = Year('Calendar'[Date])

then create measure to get the selected valaue from slicer

Selectedyear = SELECTEDVALUE('Calendar'[Year_])

Then create calculated column 

Selection Slicer = SWITCH(TRUE(),

                    [Selectedyear]=YEAR(TODAY()), "Current",

                    [Selectedyear] = YEAR(TODAY())-1, "Prior Year",

                    [Selectedyear]=YEAR(TODAY())-2, "Y-2","Others")

As you can see i got the desired result and it is dynamic. 

[RyanMarshall82]

Thanks again! So I can create this behaviour where the selectedvalue = year(today()) etc, but can this also work with a table with Years in it, so selectedvalue = data[year] - ie each row would have "CurrentYear" if data[year] = 2023, "PriorYear" if data[year]= 2022 and so forth? That's where it seems to fall down for me. 

I'm sorry, I can't work out how to upload a pbix to demonstrate! 

[Uzi2019]

Hi @RyanMarshall82 
You have to use ondrive, dropbox or ther cloud based services. You cant upload the PBI file here.

[Arpitb12]

Category = VAR SelectedYear = SELECTEDVALUE(YearTable[Year]) RETURN SWITCH( TRUE(), 'data'[year] = SelectedYear, "CurrentYear", 'data'[year] = SelectedYear - 1, "PriorYear", 'data'[year] = SelectedYear - 2, "Y-2", "Other" )

You can Try using this while the selectedvalue will select year according to the slicer

[RyanMarshall82]

Thanks so much! Am trying this: to confirm this is a column, not a measure?

[Uzi2019]

Hi @RyanMarshall82 
you have to take this as a column not measure.
for current year you can get the Current year from following formula.
Measure =year(Today()) 
this is to avoid Manual current year setting.