Ravindra BagaleCourses & study guides मराठी Track your progress

12. Data Hierarchies and Groups

12.3 Auto Date/Time Hierarchy vs a Custom Date Table

By Last updated:

Quick answer: the short, self-contained version of this lesson is the guide Turn Off Auto Date/Time in Power BI.

Start with a problem

Shahrukh's date axis shows Year › Quarter › Month › Day with calendar quarters, but finance wants Q1 = April–June.

Explain simply

Auto date/time builds a hidden calendar hierarchy for every date column. A custom Date table lets you build an FY hierarchy (FY › FY Quarter › Month).

Show the data

Order Date Auto date/time quarter FY quarter (Date table)
06-04-2026 Qtr 2 Q1
04-05-2026 Qtr 2 Q1
15-01-2027 Qtr 1 Q4

About this data: This table uses columns: Order Date, Auto date/time quarter, FY quarter (Date table). This table uses columns: Date, FY Quarter. This table uses columns: Fiscal level, Total Sales (₹). This table uses columns: Auto date/time, Custom Date table (Module 11.5). Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. File › Options › Current file › Data load › untick Auto date/time.

  2. In the Date table (11.5) add FY Quarter = "Q" & ROUNDUP('Date'[FY Month No] / 3, 0).

    Your result should look like this:

    Date FY Quarter
    01-04-2026 Q1
    01-07-2026 Q2
    01-01-2027 Q4

  3. Create the hierarchy FY › FY Quarter › Month Name (Month Name sorted by FY Month No).

    Your result should look like this:

    Date table
    Fiscal: FY › FY Quarter › Month Name

  4. Matrix: Fiscal on Rows, Total Sales on Values › expand to Month.

Show the result

Your result should look like this (fictional sample):

Fiscal level Total Sales (₹)
FY 2026-27 2,100
FY 2026-27 › Q1 2,100
Q1 › Apr 900
Q1 › May 1,200

Explain what happened

FY Month No puts April at 1, so ROUNDUP(month ÷ 3) gives Q1 for April–June. The custom hierarchy follows the Indian financial year; the automatic one cannot.

Explore further

  • With Auto date/time off, a date column shows no hierarchy unless it is related to your Date table.

Detailed reference for this lesson:

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

  1. File › Options and settings › Options.
  2. Current File › Data Load › Time intelligence › untick Auto date/time (for this file).
  3. Global › Data Load › Time intelligence › untick Auto date/time for new files (for future files).
  4. Create/mark your Date table and build the Date hierarchy from it (12.2).

Tip

Now look at this: 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.

Fix common mistakes

Wrong result Reason Correction
Hidden LocalDateTable tables make the model large Auto date/time on Turn it off
Q1 shows January–March Calendar quarter used Use the FY Quarter column
Months sort alphabetically No Sort by column Sort Month Name by FY Month No

Practise independently

Practice task

Which FY and FY quarter does 15-02-2027 belong to?

Hints
  • February is FY month 11.
Solution

FY 2026-27, Q4 (ROUNDUP(11 ÷ 3) = 4).

Think like a professional

Finance thinks in April–March. Build the FY hierarchy once in the Date table and every report agrees.

Ravi Sir's Cards for this lesson

Auto date/time vs a custom date table. Auto date/time is handy but adds hidden tables and cannot do custom fiscal years well.Revision cardWhy turn off Auto date/time in Power BI? Auto date/time creates a hidden date table for every date column. This makes the model bigger and gives separate calendars that cannot be shared or customised,Interview cardAuto date/time vs custom date table. Both give Year, Quarter, Month and Day for dates. Auto date/time is automatic and hidden. A custom date table is one you build and mark.Comparison card

मराठी cards