Hi all,
So, I have the following code that I use to create a datetable for academic years (I work in an educational institution):
Date Table =
VAR MinYear =
YEAR ( MIN ( StaffTotals[DateYear] ) )
VAR MaxYear =
YEAR ( MAX ( StaffTotals[DateYear] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO (),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Academic Calendar Year", IF ( MONTH ( [Date] ) >= 9, YEAR ( [Date] ), YEAR ( [Date] ) - 1 ),
"Academic Year", IF (
MONTH ( [Date] ) >= 9,
YEAR ( [Date] ) & "/"
& RIGHT ( YEAR ( [Date] ) + 1, 2 ),
YEAR ( [Date] ) - 1 & "/"
& RIGHT ( YEAR ( [Date] ) , 2 )
),
"Academic Year (Short)", IF (
MONTH ( [Date] ) >= 9,
RIGHT ( YEAR ( [Date] ), 2 ) & "/"
& RIGHT ( YEAR ( [Date] ) + 1, 2 ),
RIGHT ( YEAR ( [Date] ) - 1, 2 ) & "/"
& RIGHT ( YEAR ( [Date] ), 2 )
),
"Std Week Number",
WEEKNUM([Date],2
),
"Week Number",
VAR stdWeekNum =
WEEKNUM([Date],2)
VAR academicWeekNum =
stdWeekNum - WEEKNUM(DATE(YEAR([Date]),7,29),2)
RETURN
IF(academicWeekNum <= 0, 52 + academicWeekNum, academicWeekNum)
)
This will make my date table between the lowest and highest possible year in my data. However, I want to be able to add a year onto this datetable (i.e. make it longer), so that i get the values between my lowest and highest possible year plus one. (e.g. my data currently runs between 2017 and 2022. I want a datetable that runs between 2017 and 2023).
I figure that I need to make the change in my setting on the MaxYear variable but nothing works. I have tried DATEADD, a simple + 1 onto the variable, adding an additional variable and adding 1 to that, but can't get it to work.
Am I missing something simple here?
