Switch/Update slicer values after specific day each year?

[rodneyc8063_1]

Im just starting out on Power BI and have a hopefully quick question

I am using a Directy Query to my Oracle Database.

I imported a dimension table DM_CNF_TERM, where I have the following two columns (theres more but Im focused on ACAD_YEAR_DESC)

ACAD_YEAR_DESC           ACAD_YEAR_NUM

.
.
Acad 2012/13                   2012
Acad 2013/14                   2013
Acad 2014/15                   2014
Acad 2015/16                   2015
Acad 2016/17                   2016
Acad 2017/18                   2017
Acad 2018/19                   2018
.
.

Basically theres rows all the way back from 1950's to today.

I was wanting to use ACAD_YEAR_DESC in a slicer to filter a matrix/pivot table, BUT right now the slicer is returning all the rows in the column (expected from what I understand).

I was wanting to only pull the most recent 5 acad years, so I would want only the following values

Acad 2014/15
Acad 2015/16
Acad 2016/17
Acad 2017/18
Acad 2018/19

One problem I have is I want the acad years to update moving forward when its the new year. So next year I would expect the following

Acad 2015/16
Acad 2016/17
Acad 2017/18
Acad 2018/19

Acad 2019/20

In my table DM_CNF_TERM I created a new calculated column with the following logic

5_Years = SWITCH(TRUE,
DM_CNF_TERM[ACAD_YEAR_NUM]=YEAR(TODAY())-4,'DM_CNF_TERM'[ACAD_YEAR_DESC]
,DM_CNF_TERM[ACAD_YEAR_NUM]=YEAR(TODAY())-3,'DM_CNF_TERM'[ACAD_YEAR_DESC]
,DM_CNF_TERM[ACAD_YEAR_NUM]=YEAR(TODAY())-2,'DM_CNF_TERM'[ACAD_YEAR_DESC]
,DM_CNF_TERM[ACAD_YEAR_NUM]=YEAR(TODAY())-1,'DM_CNF_TERM'[ACAD_YEAR_DESC]
,DM_CNF_TERM[ACAD_YEAR_NUM]=YEAR(TODAY()),'DM_CNF_TERM'[ACAD_YEAR_DESC]
,DM_CNF_TERM[ACAD_YEAR_NUM]=YEAR(TODAY())+1,'DM_CNF_TERM'[ACAD_YEAR_DESC]
)

Which seems to be working as I expect it to.

Now I have 2 questions

1) If I wanted the ACAD_YEAR_DESC to actually switch on a certain DAY, lets say Nov 1 every year - How would I go about doing this?

2) I was wondering is there anyway I can create a totally seperate table in my model that could house this calculated column? Basically, I dont want to be adding columns in a bunch of tables. I was hoping I could create a stand alone table or location where I can store all my calculated columns.

Any insight would be very much appreciated

[parry2k]

@rodneyc8063_1 did you looked at relative data filtering which you can use at report level.