Forum Discussion
Calculated Column function to Categorise Dates for an abnormal date period
I dont think this should be too difficult... essentially, I've got a list of projects with associated completion dates in one column and I want to add a calculated column which basically states the academic year they were completed in. Academic year running between Sep 1 and Aug 31. So for example, if the project was completed on April 1, 2020, then it would be part of the academic year ended 2020 and 2020 would be the calendar year value it should spit out. For a project completed on August 31, 2021, that would be part of the academic year ended 2021.
I was thinking of using a nested if statement but no luck with the syntax. Any tips would be greatly appreciated.
Hi Anonymous
try this
NEwColumn = IF ( MONTH ( TableName[Completion_Date] ) < 9, YEAR ( TableName[Completion_Date] ) - 1, YEAR ( TableName[Completion_Date] ) )Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
2 Replies
Hi Anonymous
try this
NEwColumn = IF ( MONTH ( TableName[Completion_Date] ) < 9, YEAR ( TableName[Completion_Date] ) - 1, YEAR ( TableName[Completion_Date] ) )Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.