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

Summary

When using range-based 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 range-based 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 termination

• Time in stage or lifecycle metrics

   

Why Mixing Days and Months Causes Problems

The key issue is that months are not a fixed length of time.

• Days are always consistent
• Months vary (28, 29, 30, or 31 days)

Because of this, a single value (for example, 59 days) can fall into different month buckets depending on the calendar month it started in.

This behavior is expected and unavoidable when days and months are mixed.

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.

What This Means for Your Reports

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

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.