Forum Discussion

RuthC's avatar
RuthC
Frequent Visitor
2 years ago
Solved

Date Hierarchy

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 Sp...
Need Help
  • AilleryO's avatar
    AilleryO
    2 years ago

    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
AilleryO's avatar
AilleryO
Memorable Member
2 years ago

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's avatar
RuthC
Frequent Visitor
2 years ago

Yes, that worked. Thank you.