Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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] ) )
)
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] ) )
)
Yes, that worked. Thank you.
User | Count |
---|---|
83 | |
69 | |
68 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |