Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Custom Year column between dates

Hi, 

I need to create a custom Year column based on between dates

 

Between

01/09/2014 and 31/08/2015 then 14/15 else

01/09/2015 and 31/08/2016 then 15/16 else

01/09/2016 and 31/08/2017 then 16/17 else

01/09/2017 and 31/08/2018 then 17/18 else

01/09/2018 and 31/08/2019 then 18/19 else

01/09/2019 and 31/08/2020 then 19/20 else

01/09/2020 and 31/08/2021 then 20/21 else

01/09/2021 and 31/08/2022 then 21/22 else

01/09/2022 and 31/08/2023 then 22/23 else

 

any idea what the correct syntax to use on this would be, please?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try a new column like

 

if(month([Date]) >=9 , format([Date], "YY") & "" & ((format([Date], "YY")*1)+1) ,((format([Date], "YY")*1)-1) & "" & format([Date], "YY") )

 

https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Anonymous , Try a new column like

 

if(month([Date]) >=9 , format([Date], "YY") & "" & ((format([Date], "YY")*1)+1) ,((format([Date], "YY")*1)-1) & "" & format([Date], "YY") )

 

https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.