Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Accademic Year

Please I need help with this question below:   I have a table with StartDate and Enddate columns. I would like to create a Academic year column for my report usisng the start and end date columns  ...
  • Adescrit's avatar
    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.