Forum Discussion

luisadavidson's avatar
luisadavidson
Frequent Visitor
7 years ago
Solved

Add a column for academic year

Hi all,

 

Very new to this and have tried to follow other forum responses but to no avail!

 

I have pulled in data from 3 academic years and need to add a column to associate each row with the relevant year. e.g. if the date is between 1/8/2018 and 31/7/2019 then the academic year should be 2018/9

 

Please can anyone help? thanks

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Hello luisadavidson 

    If we start from the bottom of your date range then we only need compare that each date is below the threshold to find the right [Academic Year], like so:

    Academic Year = 
SWITCH(
    TRUE(),
    'Table'[Date] <= DATE ( 2017, 7, 31 ), "2016/7",
    'Table'[Date] <= DATE ( 2018, 7, 31 ), "2017/8",
    'Table'[Date] <= DATE ( 2019, 7, 31 ), "2018/9"
)

    This will be added as a calculated column to your table.

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Hello luisadavidson 

    If we start from the bottom of your date range then we only need compare that each date is below the threshold to find the right [Academic Year], like so:

    Academic Year = 
SWITCH(
    TRUE(),
    'Table'[Date] <= DATE ( 2017, 7, 31 ), "2016/7",
    'Table'[Date] <= DATE ( 2018, 7, 31 ), "2017/8",
    'Table'[Date] <= DATE ( 2019, 7, 31 ), "2018/9"
)

    This will be added as a calculated column to your table.