Add field to date table

Power BI
1

How can I add column to date

Power BI Date Table code.txt (2.8 KB)

SchoolYear =
SWITCH(
TRUE(),
Dates[Date] <= DATE ( 2015, 8, 31 ), “2014”,
Dates[Date] <= DATE ( 2016, 8, 31 ), “2015”,
Dates[Date] <= DATE ( 2017, 8, 31 ), “2016”,
Dates[Date] <= DATE ( 2018, 8, 31 ), “2017”,
Dates[Date] <= DATE ( 2019, 8, 31 ), “2018”,
Dates[Date] <= DATE ( 2020, 8, 31 ), “2019”,
Dates[Date] <= DATE ( 2021, 8, 31 ), “2020”,
Dates[Date] <= DATE ( 2022, 8, 31 ), “2021”
)

2

Hi,

Not clear from above explanation what you want to achieve can you elaborate what you wanna do.

Thanks

3

How can I modify the attach M code to add a field called “SchoolYear”

The attach code is Enterprise function to create Dates table

4

If there is no need to utilise the Fiscal Year column could you not simply set the Fiscal start month as September and take it from there?

1 Like
5

I do understand what you are saying about the Fiscal and start month,

But I’ll like to see a sample code because I need to create another column in the example below. I have created the 2 columns using calculated columns but I don’t want to use calculated columns. I have not put the time into learning M code as yet.

SchoolYearDesc =
SWITCH(
TRUE(),
Dates[Date] <= DATE ( 2016, 8, 31 ), “2015-2016”,
Dates[Date] <= DATE ( 2017, 8, 31 ), “2016-2017”,
Dates[Date] <= DATE ( 2018, 8, 31 ), “2017-2018”,
Dates[Date] <= DATE ( 2019, 8, 31 ), “2018-2019”,
Dates[Date] <= DATE ( 2020, 8, 31 ), “2019-2020”,
Dates[Date] <= DATE ( 2021, 8, 31 ), “2020-2021”,
Dates[Date] <= DATE ( 2022, 8, 31 ), “2021-2022”
)

6

If you are using the Extended Date table

generated by @Melissa then the M Code can be modified slightly by using the Financial Year column with the following

*AddFY = Table.AddColumn(InsertWeekEnding, "School Year", each (if [MonthOfYear] >= FYStartMonth then Text.From([Year]) & " - " & Text.PadEnd( Text.End( Text.From([Year] +1), 4), 2, "0") else Text.From([Year] - 1) & " - " & Text.End( Text.From([Year]), 4)), type text),*

I have also attached a pbix file so this can be seen.

Hope this satisfies your query.

School Year Dates
School Year Dates346×699 61.7 KB

School Year.pbix (151.0 KB)

2 Likes
7

Hi @mjmcs13 did the response provided by the other members help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @mjmcs13, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.