How to Enable Time Intelligence for Academic Semesters Without Date Columns

Hi ucfaramos 

 You should create a custom academic date table with all of the info you have listed, add start date and end date columns. Also, add an index column and any other useful columns you may need

Relate this to your fact table by BOE_TERM_ID

Since built-in time intelligence functions require a true date column, you’ll need to use Term_Index instead. Use measures like this

Enrollment YoY = 
VAR CurrentTerm = SELECTEDVALUE('Academic Calendar'[Term_Index])
VAR PreviousTerm = CurrentTerm - 2  -- Assuming 2 terms per year
RETURN
    CALCULATE(
        [Total Enrollment],
        'Academic Calendar'[Term_Index] = PreviousTerm
    )

This should work, please give a thumbs up and mark as solved if it helps, thanks!

Hi ucfaramos ,

Yes, it's absolutely possible to simulate date-based time intelligence for academic semesters in Power BI without traditional date columns. The solution involves creating a dedicated "Academic Calendar" table to establish a chronological structure and then writing custom DAX measures to perform year-over-year comparisons.

First, you need to create a new calculated table that contains a unique list of your academic terms. In the Data view of Power BI, select New Table and use the following DAX expression, replacing YourDataTable with the name of your table. This will form the foundation of your custom calendar.

Academic Calendar =
SUMMARIZE (
    YourDataTable,
    YourDataTable[ACAD_YEAR],
    YourDataTable[TERM_DESC],
    YourDataTable[BOE_TERM_ID]
)

Next, you'll enrich this new Academic Calendar table with calculated columns to enable sorting and relationships. You'll create a numeric Year Start column from ACAD_YEAR, a Term Order column to numerically represent Fall, Spring, and Summer, and finally, an Academic Year-Term Sort column that combines the year and term order for proper chronological sorting across all years.

Year Start = VALUE(LEFT('Academic Calendar'[ACAD_YEAR], 4))

Term Order =
SWITCH (
    TRUE (),
    CONTAINSSTRING ( 'Academic Calendar'[TERM_DESC], "Fall" ), 1,
    CONTAINSSTRING ( 'Academic Calendar'[TERM_DESC], "Spring" ), 2,
    CONTAINSSTRING ( 'Academic Calendar'[TERM_DESC], "Summer" ), 3,
    4
)

Academic Year-Term Sort = 'Academic Calendar'[Year Start] * 10 + 'Academic Calendar'[Term Order]

With the calendar table complete, go to the Model view and create a relationship by dragging BOE_TERM_ID from your new Academic Calendar table to the corresponding column in your main data table. To ensure visuals always display terms in the correct order, select the TERM_DESC column in the Data view and use the Sort by column feature to sort it by your Academic Year-Term Sort column.

Finally, you can write DAX measures that leverage this new structure. To compare a value with the same semester in the previous academic year, you can create a measure like this, making sure to insert your base measure (e.g., SUM(YourDataTable[Enrollment])).

Value Last Year =
CALCULATE (
    [Your Base Measure],
    FILTER (
        ALL ( 'Academic Calendar' ),
        'Academic Calendar'[Year Start] = MAX ( 'Academic Calendar'[Year Start] ) - 1 &&
        'Academic Calendar'[Term Order] = MAX ( 'Academic Calendar'[Term Order] )
    )
)

From there, calculating the Year-over-Year change and percentage change is straightforward.

YoY Change = [Your Base Measure] - [Value Last Year]

YoY % Change = DIVIDE ( [YoY Change], [Value Last Year] )

Best regards,