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
Look, friends: leaving Auto date/time on while 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. Practise, and it will feel very easy.
Ravindra Bagale's Tip – मराठी
हे बघा मित्रांनो: custom Date table असतानाही Auto date/time चालू ठेवणं ही common चूक आहे. Model प्रत्येक date column साठी एक hidden date table बाळगतं, आणि students ना field list मध्ये दोन वेगळ्या "Date" hierarchies दिसतात. Auto date/time बंद करा आणि तुमच्या स्वतःच्या Date table वरून hierarchies बनवा. Practice करा, मग एकदम सोपं वाटेल.
Ravindra Bagale's Tip – हिंदी
देखो दोस्तों: custom Date table होते हुए भी Auto date/time चालू रखना common गलती है. Model हर date column के लिए एक hidden date table रखता है, और students को field list में दो अलग "Date" hierarchies दिखती हैं. Auto date/time बंद करो और अपनी Date table से hierarchies बनाओ. Practice करो, फिर बहुत आसान लगेगा.