Converting Month into custom Year column

[Anonymous]

Hi there. 

I have a table (Dates) that looks like this.

Month	Year
Jan 2017	2017
Feb 2017	2017
May 2018	2018
September 2019	2019
February 2020	2020 (pre covid)
March 2020	2020 (post covid)
June 2020	2020 (post covid)
Jan 2021	2021

I am looking to create a column (in red) to explore the impact pre and post covid so that it will just give me the year for 2018, 2019 and 2021 but for 2020 it splits it into two i.e. Jan 2020 and Feb 2020 = 2020 (pre covid) and March 2020 onwards = 2020 (post covid)

Could anyone help with this as this is quite complex?

[ryan_mayu · Accepted solution]

@Anonymous 

do you have a date column? if you have a date column, you can try this

year = if(year('Date'[Date])=2020 && month('Date'[Date]) in {1,2},"2020 pre covid",if(year('Date'[Date])=2020,"2020 post covid",FORMAT('Date'[Date],"yyyy")))

[Anonymous]

@Anonymous Create next calculated columns:

'Year' - original year;

'Month' - month's number (january - 1, february - 2 etc.)

'customYear': SWITCH(TRUE(),

                       'Year'=2020&&'Month'<=2,"2020 (pre covid)",

                       'Year'=2020&&'Month'>2,"2020 (post covid)",

                       'Year')