12. Data Hierarchies and Groups
12.3 Auto Date/Time Hierarchy vs a Custom Date Table
When Auto date/time is on, Power BI creates a hidden date table for every date column. When you drag Order Date into a visual, you see Year, Quarter, Month, Day automatically.
| Auto date/time | Custom Date table (Module 11.5) | |
|---|---|---|
| Setup | Automatic | You create it (DAX/Power Query) and Mark as date table |
| Levels | Year, Quarter, Month, Day only | Anything: Fiscal Year (Apr–Mar), Week, Festival, Is Weekend |
| Model size | One hidden table per date column (can bloat the model) | One table |
| Time intelligence | Works only per column | Works across the model with one set of measures |
| Indian financial year | Not possible | Yes |
| Recommended for | Quick exploration | All real projects |
Steps in Power BI – turn Auto date/time off
- File › Options and settings › Options.
- Current File › Data Load › Time intelligence › untick Auto date/time (for this file).
- Global › Data Load › Time intelligence › untick Auto date/time for new files (for future files).
- Create/mark your Date table and build the Date hierarchy from it (12.2).
Tip
Aata he bagha: if a visual shows "Year, Quarter, Month, Day" under Order Date with a calendar icon, you are using the auto hierarchy, not your Date table. Use 'Date'[Date] or your custom hierarchy instead.
Ravindra Bagale's Tip
He bagha, mitrano: leaving Auto date/time on and also having a custom Date table is a common mistake. The model carries a hidden date table for every date column, and students get two different "Date" hierarchies in the field list. Turn off Auto date/time and build hierarchies from your own Date table. Practice kara, mag ekdum sope vatel.