Forum Discussion
Accademic Year
- 6 years ago
I would start off by creating a calendar table.
In the Modelling tab at the top of Power BI desktop, click on "New Table", and then enter the following code:
Calendar = ADDCOLUMNS ( CALENDAR ( MIN ( 'Academic Years'[Start Date] ), MAX ( 'Academic Years'[Start Date] ) ), "DateAsInteger", FORMAT ( [Date], "DDMMYYYY" ), "Year", YEAR ( [Date] ) )Note I'm assuming here that your start date and end date columns are in a separate table called 'Academic Years'.
This will create a new table in which the first column is a list of dates between the start of the first academic year and the end of the final academic year in your initial 'Academic Years' table.
Now you can create a new column in your Calendar table that identifies which academic year each date falls into.
Academic Year = IF ( MONTH('Calendar'[Date]) <= 9, "AY" & RIGHT( VALUE('Calendar'[Year]),2)-1 & "/" & RIGHT( VALUE('Calendar'[Year]),2), "AY" & RIGHT( VALUE('Calendar'[Year]),2) & "/" & RIGHT( VALUE('Calendar'[Year]),2) + 1 )You can then use this column as a filter to select which academic year you would like to view. (Ensure the 'Calendar' table is connected to the data you are filtering first.
I would start off by creating a calendar table.
In the Modelling tab at the top of Power BI desktop, click on "New Table", and then enter the following code:
Calendar =
ADDCOLUMNS (
CALENDAR (
MIN ( 'Academic Years'[Start Date] ),
MAX ( 'Academic Years'[Start Date] )
),
"DateAsInteger", FORMAT ( [Date], "DDMMYYYY" ),
"Year", YEAR ( [Date] )
)
Note I'm assuming here that your start date and end date columns are in a separate table called 'Academic Years'.
This will create a new table in which the first column is a list of dates between the start of the first academic year and the end of the final academic year in your initial 'Academic Years' table.
Now you can create a new column in your Calendar table that identifies which academic year each date falls into.
Academic Year =
IF (
MONTH('Calendar'[Date]) <= 9,
"AY"
& RIGHT( VALUE('Calendar'[Year]),2)-1 & "/" & RIGHT( VALUE('Calendar'[Year]),2),
"AY"
& RIGHT( VALUE('Calendar'[Year]),2) & "/" & RIGHT( VALUE('Calendar'[Year]),2) + 1
)
You can then use this column as a filter to select which academic year you would like to view. (Ensure the 'Calendar' table is connected to the data you are filtering first.
- Anonymous6 years agoNot applicable
Thanks for your help, But am not sure if this possible, I want my report to be filtered by Academic Year (Periods) and also an exact date for example, if I want my report to display student that attended a session between 01/01/2020 and 31/01/2020.