Format dates for automatic time intelligence
Time intelligence automatically recognizes the date fields you assign to a visual and interprets them as a date hierarchy (for example, Year → Quarter → Month → Day). After plan detects a date/time hierarchy, it maps each member to the corresponding calendar period. Use the hierarchy for time intelligence calculations such as year-to-date (YTD), prior period, next N periods, and period-over-period comparisons.
This article is intended primarily for report developers to understand the time hierarchies that plan detects:
Supported hierarchy levels
Accepted text and numeric label formats
Supported composite (multi-part) labels
Valid hierarchy orderings
Unrecognized labels along with recommendations to resolve them.
Time intelligence automatically detects the hierarchy without additional configuration when the labels follow one of the supported formats described in this article.
How plan detects a time hierarchy
Time intelligence identifies the date hierarchy by using the following order of precedence:
Explicit configuration: If a field is explicitly mapped to a time hierarchy level and label format, that mapping takes precedence over all other detection methods.
Field name: If no explicit mapping exists, Time Intelligence examines the field name. Names containing terms such as Year, Yr, Half Year, Half, Quarter, Qtr, Month, Week, or Day are mapped to the corresponding hierarchy level.
Field values: If the field name is not sufficient to determine the hierarchy level, Time intelligence analyzes the member values (labels) and matches them against the supported formats and keywords described in this article.
After the hierarchy levels are identified, Time intelligence interprets each level in the context of its parent level. For example, a Quarter under Year is interpreted as a quarter within that year, and a Day under Month is interpreted as a day within that month. This contextual relationship enables accurate time-based calculations across the hierarchy.
Best practices while importing date/time fields
Use one period per member. Each Year member should contain a single year, each Quarter a single quarter, and so on. Avoid ranges such as 2025-2026 or Q1-Q2.
Keep formats consistent within a level. Do not mix Jan and 1, or Q1 and Quarter 1, in the same field.
Order levels from broad to granular. Always place Year at the top, followed by Half Year / Quarter, then Month or Week, then Day.
Name your fields clearly. Field names that include Year, Quarter, Month, Week, or Day help the product recognize levels immediately.
Prefer standard hierarchies. The built-in Power BI date hierarchy (Year → Quarter → Month → Day) is fully supported and is the most reliable choice.
Prefer separate levels over composite labels. Splitting periods into their own fields is clearer and easier to maintain than packing multiple periods into one label.
Use ISO dates for day-level fields. When a field holds full dates, format them as YYYY-MM-DD (optionally with a 00:00:00 time component).
Include a Month level before Day. When you need daily granularity, keep a Month level between the parent period and the Day level for the most predictable results.
Supported hierarchy levels
If your data uses the following formats, Time intelligence detects your time hierarchy automatically without any explicit configuration.
Year
A single calendar or fiscal year represented as a four-digit or two-digit value. Two-digit years are interpreted as belonging to the current century. For example, 26 is interpreted as 2026.
2025-2026 (Year ranges are not allowed), 25-26, FY26A
Half Year
One of the two halves of a year.
3, H3, First Half (spelled-out words)
Quarter
One of the four quarters of a year.
Q5, Q0, 5
Month
A calendar month — numbers 1 to 12, full month names, or standard three-letter abbreviations.
13, 0, Sept
Week
A week within a year: standard calendar — weeks 1 through 53.
0, 54, WK1 (The prefix WK is not supported)
Day
A day within a month or a complete date.
32, 01/02/2026, Feb 1, 2026, 01-Feb-2026
Supported formats
The following tables list supported label formats for each level. Formats are case-insensitive; for example, january, January, and JANUARY are accepted.
Year formats
Two-digit year
25
FY + four-digit year
FY2025
FY + two-digit year
FY25
FY + four-digit year (with space)
FY 2025
FY + two-digit year (with space)
FY 25
Half-year formats
Single digit
1, 2
H prefix
H1, H2
Two digit
01, 02
Quarter formats
Single digit
1, 2, 3, 4
Q prefix
Q1, Q2, Q3, Q4
Two digit
01, 02, 03, 04
Qtr prefix
Qtr1, Qtr2
Qtr + space
Qtr 1, Qtr 2
Quarter + space
Quarter 1, Quarter 2
Month formats
Single-digit number
1, 9, 12
Two-digit number
01, 09, 12
Short name
Jan, Feb, Dec
Full name
January, February, December
Week formats
Single/multi-digit number
1, 9, 53
Two-digit number
01, 09, 53
W prefix
W1, W9, W53
W + two digit
W01, W09, W53
Week + number
Week 1, Week 53
Week + two digit
Week 01, Week 09
Day formats
Single-digit day
1, 9, 31
Two-digit day
01, 09, 31
ISO date
2025-02-01
ISO date with time
2025-02-01 00:00:00
ISO date with T separator
2025-02-01T00:00:00
Several levels share the same numeric labels; for example, 1 can be a half-year, quarter, month, week, or day. Time intelligence resolves ambiguity using the field name and the parent level in the hierarchy. Keeping levels clearly named and correctly ordered ensures the right interpretation.
Supported composite formats
A composite label packs more than one period into a single member value—for example, a quarter and a year together in one label. Composite labels are supported when the field is mapped to a matching label format.
For standard reporting, opt for a clean multi-level hierarchy (each level in its own field) over packing several periods into one label. Composite labels are best reserved for pre-formatted source data where a single column already contains the full period description.
For every composite label:
Describe the parts clearly. Each recognizable part (year, quarter, month, and so on) is extracted and used to place the member on the correct period.
Provide consistent examples. All members of the same field should follow the same composite structure.
Common composite patterns include:
Quarter + Year
Q1-FY24, Q1 FY 24
Quarter and Year
Full date (Year + Month + Day)
2025-02-01
Year, Month, and Day in one label
A composite label can contain at most one value per level. It can't contain a range (such as two years or two quarters) in a single member.
Supported hierarchy patterns
Hierarchies must always flow from the broadest time bucket down to the smallest granular bucket.
If you place a Day column directly under a Quarter or Half Year without a Month column in between, Time intelligence counts days relative to the start of that quarter. Always insert a Month layer above your days to keep your visuals predictable.
✅ Valid Top-to-Bottom Paths
Year
Year → Half Year
Year → Quarter
Year → Month
Year → Week
Year → Half Year → Quarter
Year → Half Year → Month
Year → Half Year → Week
Year → Quarter → Month
Year → Quarter → Week
Year → Month → Week
Year → Month → Day
Year → Week → Day
Year → Quarter → Month → Day
A standalone date column (e.g., 2026-02-01) is also fully supported.
❌ Invalid hierarchies that can cause calculation errors.
Quarter → Year (Reversed)
Month → Quarter (Reversed)
Day → Month
Day → Year
Week → Month
Accepted date strings
The following text keywords are recognized by name when the product inspects field values. All of them are case-insensitive.
Years
2025 (four-digit)
25 (two-digit, read as the current century)
FY2025, FY 2025, FY25, FY 25
Half Years
H1, H2
Quarters
Q1, Q2, Q3, Q4
Qtr 1, Qtr 2, Qtr 3, Qtr 4
Qtr_1, Qtr_2, Qtr_3, Qtr_4
Qtr-1, Qtr-2, Qtr-3, Qtr-4
Quarter 1, Quarter 2, Quarter 3, Quarter 4
Quarter_1, Quarter_2, Quarter_3, Quarter_4
Months
Short names: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
Full names: January, February, March, April, May, June, July, August, September, October, November, December
Weeks
Week 1 through Week 53
Week_1 through Week_53
W1 through W53
Field-name keywords
Time intelligence identifies hierarchy levels from field names by recognizing the following keywords: Year, Yr, Half Year, Half, Quarter, Qtr, Month, Week, and Day.
Unsupported formats
The formats below are not recognized. In each case, time intelligence can't determine which period the label refers to, so the member is left out of the date hierarchy.
22-23
Contains two years in a single member. Each member must represent exactly one period.
2025-2026
A range spanning more than one year. Ranges are not supported at the Year level.
Q5, Q0
Quarters only exist as 1–4.
Month 13, 0
Months only exist as 1–12.
Week 54, 54
Weeks only exist as 1–53.
Day 32, 32, 0
Days only exist as 1–31.
Sept
Only the standard Sep abbreviation (or the full September) is recognized.
WK1
The WK prefix is not a supported week format; use W1 or Week 1.
First Half, Half 1
Spelled-out half-year forms are not recognized; use H1/H2.
01/02/2025, Feb 1, 2025, 01-Feb-2025
Only ISO-style dates (2025-02-01) are recognized at the Day level.
Mixed styles in one field (e.g. some Jan, some 1)
All members of a level must use the same format so the product can interpret them consistently.
Reversed order (e.g. Quarter above Year)
Levels must run from broadest to most granular.
Examples
A standard four-level hierarchy: Interpreted as: 1 January 2025, within Q1 2025.
Year
2025
Quarter
Q1
Month
Jan
Day
01
A fiscal-year hierarchy
Year
FY2025
Half Year
H1
Month
January
A single full-date field
Day
2025-02-01, 2025-02-02, 2025-02-03
Each value already identifies a specific day, so no additional levels are required.
4. A composite quarter-and-year field: Both the quarter and the year are extracted from each label.
Q1 FY 24, Q2 FY 24, Q3 FY 24, Q4 FY 24
Troubleshooting
Time intelligence does not detect my hierarchy.
Check that each field uses one of the accepted formats listed above and that all members of a level use the same format. Rename the field to include a recognizable keyword (for example, Year or Month) so the level is identified. Confirm that the values fall within the valid range for the level (Quarters 1–4, Months 1–12, Weeks 1–53, Days 1–31).
The hierarchy is detected in the wrong order.
Levels must be arranged from broadest to most granular (Year at the top, Day at the bottom). Reorder the fields so that each level sits above its subdivisions.
A member is missing or ignored.
This usually means the label falls outside the supported range or uses an unsupported format—for example, Q5, Week 54, Sept, or a range such as 22-23. Correct the label to a supported value or format.
A composite date is not parsed correctly.
Ensure every member of the field follows the same composite structure and that each period part is in a supported format. A composite label can hold only one value per level and cannot contain a range.
Two-digit years land in the wrong century.
Two-digit years are interpreted within the current century (for example, 24 becomes 2024). If you need a specific century, use the full four-digit year.
Days appear under the wrong month.
When a Day level sits directly under a Quarter or Half Year without a Month level, days are measured from the start of that parent period. Add a Month level above Day for accurate placement.
Last updated
Was this helpful?