Why Days and Months Should Not Be Mixed in Duration-Based Range Dimensions

Summary

When using duration-based range dimensions (e.g. tenure, time in role), days and months should not be mixed within the same dimension. Although this may seem intuitive, mixing these units can cause duplicate values, missing records, and inconsistent totals in reports.

This article explains why this happens and outlines recommended alternatives.
 

What Is a Range-Based Dimension?

A duration-based range dimension groups numeric or time-based values into buckets, such as:

• < 90 Days
• 3-6 Months
• 1-2 Years

These dimensions are commonly used for:

• Employee tenure
• Time to Separation

• Time in Stage or Lifecycle

   

Why Mixing Days, Months and Years Causes Problems

While days and weeks are mathematically fixed (1 week is always 7 days), days cannot be safely mixed with months or years because their lengths are inconsistent.

• Weeks: Are always 7 days
• Months: Variable (28, 29, 30, or 31 days)
• Years: Variable (365 or 366 days due to leap years)

Because of this variation, a single value (e.g., 59 days) is ‘homeless’, it could fall into different month buckets depending on the specific calendar month the count started in. This behavior is an unavoidable mathematical reality when mixing these units.

Example: How Duplicates Can Occur

Imagine a dimension structured like this:

• Level 1: < 90 Days, 3-6 Months
• Level 2:
  • 0-30 Days
  • 31-60 Days
  • 61-90 Days
  • 1-2 Months
  • 2-3 Months

A value of 59 days might:

• Be counted as 31-60 Days
• Also appear as 2 Months, depending on the month length

This can result in:

• The same employee appearing in multiple buckets
• Totals changing when you drill up or down
• Confusing or inconsistent report results

Why Adjusting the Ranges Doesn’t Fix It

You might expect that redefining ranges (for example, < 90 Days vs. < 3 Months) would solve the issue but it does not.

Three months can equal:

• 89 days
• 90 days
• 91 or 92 days

Six months can range from 181 to 184 days, depending on the start month.

Because of this variability, there is no reliable way to align days and months without overlap or gaps.

Weeks Have the Same Limitation

Weeks introduce similar challenges when combined with months or years. While weeks are consistently seven days long, calendar months do not divide evenly into weeks, and years may contain 52 or 53 weeks.
As a result, mixing weeks with months or years can produce the same boundary and aggregation issues described for days.

What This Means for Your Data

If days and months are mixed in a range-based dimension, you may see:

• Duplicate counts
• Missing values
• Totals that don’t add up as expected
• Confusion around boundary values (for example, exactly 6 months or exactly 1 year)

These are not calculation errors, they are a limitation of calendar-based time units.

Recommended Approach

To ensure accurate and consistent reporting, One Model recommends the following:

Use a Single Time Unit per Dimension

Choose one unit for the entire dimension:

• Days only
• Months only
• Years only

Months and years can safely coexist because their relationship is fixed (12 months = 1 year), whereas days or weeks do not align consistently with calendar months or years.
This ensures clean rollups and predictable results.
 

Separate Time Units by Clear Ranges (When needed)

If multiple units are required:

• Use distinct, non-overlapping ranges
• Keep days, months, and years clearly separated
• Follow standard dimension templates where available

This approach avoids ambiguity and prevents duplicate values.

What Is Not Supported

The following patterns are not recommended and may produce inconsistent results:

• Mixing days and months in the same hierarchy
• Rolling days directly into months
• Using calendar months to define fixed-day ranges
• Expecting exact alignment between days, months, and years

Key Takeaway

Mixing days and months in a range-based dimension will inevitably lead to overlaps or missing values due to the variable length of months.

For reliable reporting:

• Avoid mixed time units
• Use consistent measurement across buckets

If you have questions about configuring time-based dimensions, contact One Model support for guidance.