Creating Fiscal Year Text column in date table

[hannahs5]

I'm trying to find a way to connect my date table to a table that includes students' grade reports. The student grade report does not have a true date column. There are two columns that contain text that indicate the school year and term that the grades were earned, like below:

School Yr	Term
202122	Fall
202223	Winter
201920	Spring
201819	Winter

My thought is that I should create a column in my date table that identifies all dates from July 1, 2018-June 30, 2019 as the text 201819 (and all dates from July 1, 2019-June 30, 2020 as 201920 etc) so that I can relate it to the School Year column of the grade reports.

Assuming it's possible, how can I use DAX to create this column?

[FreemanZ · Accepted solution]

hi @hannahs5 

in your date table, try to add a column with this

AcadYear =

VAR _year = YEAR([Date])

VAR _month = MONTH([Date])

RETURN

IF(

    _month>=7,

    _year&RIGHT(_year+1, 2),

    (_year-1)&RIGHT(_year, 2)

)

[FreemanZ]

checked and it worked like this: