When Semi-Additive Metrics Fall Short: Why You Need Sequence Logic for Continuous-Day Engagement

[techies]

Understanding user behavior in learning systems, SaaS platforms, or product telemetry often starts with semi-additive calculations. These are the backbone of any time-series analysis, helping us compute “users as of the end of the month,” “active users this quarter,” or “retention rate.”

But semi-additive metrics only tell what happened at a point in time.

They do not reveal behavioral continuity.

They tell you what happened, not how consistently it happened.

For example, semi-additive modeling cannot answer questions such as:

• How many days did a user stay active without missing a day?
• Where does engagement typically break down?
• Which users demonstrate consistent behavior?
• How long does user momentum last?

These questions highlight critical dependencies between data points, which include:

• Non-additive
• Non-semi-additive
• Not solvable using simple time intelligence

To address these questions, a solution requires sequence analysis, specifically using run-length encoding through DAX or SQL window functions.

Implementing Continuous-Day Logic (DAX)

Assume you have a table: user_dailyactivitydate

Columns: userid, ActivityDate

Step 1: Here, the FILTER() creates a temporary table containing only the rows for the same user where the activity date is before the current row’s ActivityDate (using EARLIER to refer to the outer row).

CALCULATE(MAX(...)) then finds the latest ActivityDate from that filtered table, giving you the previous activity date.

prev_activity_date = 
CALCULATE (
    MAX ( user_dailyactivitydate[ActivityDate] ),
    FILTER (
        user_dailyactivitydate,
        user_dailyactivitydate[userid] = EARLIER ( user_dailyactivitydate[userid] ) &&
        user_dailyactivitydate[ActivityDate] < EARLIER ( user_dailyactivitydate[ActivityDate] )
    )
)

Step 2: Identify whether the current date continues the sequence

is_continuous = 
IF (
    user_dailyactivitydate[ActivityDate] - user_dailyactivitydate[prev_activity_date] = 1,
    1,
    0
)

It checks whether today’s activity date is exactly 1 day after the previous activity date.

If yes → it marks the row as 1 (continuous streak)

If no → it marks it as 0 (streak broken)

Step 3: Assign run groups

run_group = 
CALCULATE(
    COUNTROWS(user_dailyactivitydate),
    FILTER(
        user_dailyactivitydate,
        user_dailyactivitydate[userid] = EARLIER(user_dailyactivitydate[userid]) &&
        user_dailyactivitydate[ActivityDate] <= EARLIER(user_dailyactivitydate[ActivityDate]) &&
        user_dailyactivitydate[is_continuous] = 0
    )
)

run_group computes a sequential segment identifier by counting streak-breaking rows for each user up to the current row, enabling DAX to cluster activity into discrete consecutive-day streaks.

Step 4: Compute the length of each run

consecutive_days = 
CALCULATE(
    COUNTROWS(user_dailyactivitydate),
    ALLEXCEPT(user_dailyactivitydate, user_dailyactivitydate[userid], user_dailyactivitydate[run_group])
)

This column computes the length of each streak by counting the number of rows that belong to the same user and the same streak segment (run_group).

CALCULATE evaluates COUNTROWS() under a modified filter context. ALLEXCEPT removes all filters from ActivityLog, only keeping the filters on the userid and run_group

Once we have calculated consecutive_days for each run group, we can determine the user’s ongoing streak, the length of the current continuous engagement segment.

Current Streak = 
VAR LastActivityDate =
   MAX ( user_dailyactivitydate[ActivityDate] ) 

VAR LastRunGroup =
    CALCULATE (
        MAX ( user_dailyactivitydate[run_group] ),
        user_dailyactivitydate[ActivityDate] = LastActivityDate
    )

RETURN
    CALCULATE (
        MAX ( user_dailyactivitydate[consecutive_days] ),
        user_dailyactivitydate[run_group] = LastRunGroup
    )

The Current Streak measure derives the user’s active streak by anchoring the calculation on their most recent activity date. It first identifies the latest ActivityDate, then resolves the corresponding run_group that represents the user’s current streak segment. Using that run_group as the filter context, it returns the maximum consecutive_days value, which is the length of the user’s ongoing streak. This approach ensures the measure is dynamic, context-aware, and always reflects the user’s latest contiguous activity window.

Now you have a continuous engagement metric that behaves differently from standard time intelligence calculations.

• It reveals pattern-based behavior, not just totals
• Helps predict drop-off risk
• Useful for cohort analysis and product adoption analytics
• Provides granularity that snapshots cannot show

When combined with semi-additive metrics, consecutive-day logic closes the behavioral analytics gap.

This combination of snapshot-based and sequence-based metrics gives you a richer, more predictive, and more actionable analytical foundation.

Download the sample file used in this blog from here.