Forum Discussion
Establishing academic year
I have a table which includes events that happened on single date. The date format is 5/9/2016, for example. I need to report that the event happened in AY2015-16. The academic years run 7/1/yyyy-6/30yyyy. The data table includes data from 2015-16 to 2019-20.
I think I need something like this code, but do not know how to compose it so that reports the correct year: =List.InsertRange([#"Sth Last Attend Date "],0,2019-20)
Please create the following calculate column to get the expected result.
Year = IF([Date].[MonthNo]<=6&&[Date].[Day]<=31,CONCATENATE([Date].[Year]-1,CONCATENATE("-",RIGHT([Date].[Year],2))),CONCATENATE([Date].[Year],CONCATENATE("-",RIGHT([Date].[Year]+1,2))))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
JamesUhlenkamp , the expected output is not very clear. Please refer
Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441amitchandak, does this help?
Do I need to create a calendar table to define the displayed Desired Outcome column? Note: in the example, all the dates are in May or December, but the reality is that different tables (such as retention or assessment) will have dates in every month.
Samarth_18Community Champion
Hi JamesUhlenkamp ,
You can create a column with below code:-
Column = VAR years = YEAR ( 'Table (6)'[Graduation date] ) RETURN YEAR ( 'Table (6)'[Graduation date] ) - 1 & "-" & INT ( FORMAT ( 'Table (6)'[Graduation date], "YY" ) )output:-
Thanks,
Samarth
Please create the following calculate column to get the expected result.
Year = IF([Date].[MonthNo]<=6&&[Date].[Day]<=31,CONCATENATE([Date].[Year]-1,CONCATENATE("-",RIGHT([Date].[Year],2))),CONCATENATE([Date].[Year],CONCATENATE("-",RIGHT([Date].[Year]+1,2))))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
