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
