Forum Discussion

rodneyc8063_1's avatar
7 years ago

Switch/Update slicer values after specific day each year?

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