Academic/fiscal years in yy/yy format

Power Query
1

I am trying to use the extended date table to put in an academic year in the format of e.g. 20/21, 19/20 etc. 1st August is the start of the academic year for us (i.e. 01/08/2020 is the start of year 20/21, which ends on 31/07/2021). I wanted to just add this to the end of the date table in PQ, but struggled with the commands to use. I can create a calculated column in DAX just fine, but wanted to have it in PQ so I can save it and easily reuse it in future reports. The best I have been able to get to is e.g. 20/20, 19/19. I no longer have the code I was unsuccessfully using because I resorted to DAX, but I was getting stuck on being able to add or subtract from the last two digits of the year because they were text. Does anyone have any ideas? As I say, I have managed to get by through DAX, but it isn’t how I would like to achieve this.

1 Like
2

Hi @The_Bishop,

Give this a go.

image
image707×130 12 KB

Here’s that piece of M code.

AddAY = Table.AddColumn( ReorderColumns, "Academic Year", each 
  if [MonthOfYear] >= 8 
  then Text.End( Text.From([Year]), 2) & "/" & Text.End( Text.From([Year] +1), 2)
  else Text.End( Text.From([Year] -1), 2) & "/" & Text.End( Text.From([Year]), 2), type text)
  in
AddAY, documentation = [

.
I hope this is helpful.

3 Likes
3

Hello, @Melissa. That is wonderful. Thank you so much, I really appreciate your assistance.

2 Likes