topic Re: Creating Fiscal Year Text column in date table in DAX Commands and Tips

Creating Fiscal Year Text column in date table

hannahs5 — Fri, 16 Dec 2022 20:14:16 GMT

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?

Re: Creating Fiscal Year Text column in date table

FreemanZ — Sat, 17 Dec 2022 00:00:37 GMT

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)

)

Re: Creating Fiscal Year Text column in date table

FreemanZ — Sat, 17 Dec 2022 01:08:46 GMT

checked and it worked like this: