Date Hierarchy

[RuthC]

I am new at Power BI and want to visualize assessment data for a school. I need dates to appear in terms of School Year, not calendar year. I would like to be able to view scores by Fall (Aug) and Spring (May) and see trends over school years (2020-21, 2021-22, 2022-23, etc). What is the best way to set up a date table for this purpose?

[AilleryO · Accepted solution]

Hi,

To help you start here is a formula in DAX, cretaing a table with CALENDARAUTO included into ADDCOLUMNS function to create other columns.

The structure is not too complicated so you should be able to add columns according to your needs.

Not sure about the system you want, my example takes month 8 (august) as the switch from School year 2022-2023 to 2023-2024.

Use this formula by creating a new table :

TableDateSchool = ADDCOLUMNS( CALENDARAUTO() ,

"Year", YEAR([Date]),

"Year-Month", FORMAT( [Date] , "yyyy-mm"),

"SchoolYear", IF( MONTH([Date])>8 , "SY"&YEAR( [Date] )&"-"&YEAR( [Date] ) +1 , "SY"&YEAR( [Date] )-1&"-"&YEAR( [Date] ) )

)

Let us if it works or tell us what's missing

[RuthC]

Yes, that worked. Thank you.