cancel
Showing results for
Did you mean:
Helper I

## Converting Month into custom Year column

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?

1 ACCEPTED SOLUTION
Super User

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")))``

Proud to be a Super User!

2 REPLIES 2
Super User

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")))``

Proud to be a Super User!

Solution Supplier

@djordan27 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')

Announcements