Projecting Active Users with Power BI: A Dynamic Approach Using DAX

Tracking active users is a crucial metric for any software or service, helping businesses understand how their product is performing and what trends are emerging. In this article, we'll dive into a powerful method for projecting active users using Power BI's DAX functionality, focusing on a dynamic calculation that blends historical data from the previous month with live data from the current month.

The code below demonstrates how to create a projection of active users for the current month, gradually relying more on the actual data as more days pass.

The Challenge: Creating an Accurate Projection

When forecasting user engagement, businesses often face the challenge of balancing historical data with current trends. The projection needs to be dynamic, factoring in the real-time user count while still considering the trends from previous months.

This is where Power BI's DAX language comes into play. With the code provided, we create a weighted projection that smoothly transitions from relying on the previous month’s total to emphasizing the current month’s data as the days progress.

_Active Users - This Month Projected (Weighted) = 
VAR CurrentMonthStart = EOMONTH(TODAY(), -1) + 1  // First day of the current month
VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)  // Last day of the current month
VAR TodayDate = TODAY()  // Today's date
VAR DaysInMonth = DAY(CurrentMonthEnd)  // Total days in the current month
VAR DaysElapsed = DATEDIFF(CurrentMonthStart, TodayDate, DAY) + 1  // Number of days so far in this month

-- Calculate the total active users for the current month up to today
VAR CurrentMonthTotal = 
    CALCULATE(
        SUM('Visual Studio & Non-VS - Users - Monthly'[Non-VS - Active Users]) + 
        SUM('Visual Studio & Non-VS - Users - Monthly'[VS - Active Users]),
        'Visual Studio & Non-VS - Users - Monthly'[YearMonth] >= CurrentMonthStart &&
        'Visual Studio & Non-VS - Users - Monthly'[YearMonth] <= TodayDate
    )

-- Calculate the average active users per day so far this month
VAR CurrentMonthAvgPerDay = DIVIDE(CurrentMonthTotal, DaysElapsed)

-- Project the total active users for the entire month (based on the average so far)
VAR ProjectedCurrentMonthTotal = CurrentMonthAvgPerDay * DaysInMonth

-- Use the previously calculated _Previous Month Active Users measure
VAR PreviousMonthTotal = [_Active Users - Last Month]

-- Calculate a weighting factor that gradually gives more weight to the current month data as more days pass
VAR WeightFactor = DIVIDE(DaysElapsed, DaysInMonth)  // This will gradually increase as the month progresses

-- Calculate the adjusted projection using a weighted average of last month's total and the current month's projection
VAR AdjustedProjection = 
    (WeightFactor * ProjectedCurrentMonthTotal) + ((1 - WeightFactor) * PreviousMonthTotal)

-- Return the projected active users for this month, adjusted with a gradual weighting system
RETURN
    AdjustedProjection        

Breaking Down the Code: Key Components

The DAX measure _Active Users - This Month Projected (Weighted) is built step-by-step to create a reliable projection of active users. Let’s break it down:

1. Define the Timeframe

VAR CurrentMonthStart = EOMONTH(TODAY(), -1) + 1
VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)
VAR TodayDate = TODAY()
VAR DaysInMonth = DAY(CurrentMonthEnd)
VAR DaysElapsed = DATEDIFF(CurrentMonthStart, TodayDate, DAY) + 1        

These variables establish the timeframe. We calculate the start and end dates of the current month and determine how many days have passed so far. The DaysElapsed variable helps track progress within the month, which we’ll use to gradually increase the weighting towards the current month’s data.

2. Calculate Active Users So Far

VAR CurrentMonthTotal = 
    CALCULATE(
        SUM('Visual Studio & Non-VS - Users - Monthly'[Non-VS - Active Users]) + 
        SUM('Visual Studio & Non-VS - Users - Monthly'[VS - Active Users]),
        'Visual Studio & Non-VS - Users - Monthly'[YearMonth] >= CurrentMonthStart &&
        'Visual Studio & Non-VS - Users - Monthly'[YearMonth] <= TodayDate
    )        

Next, we calculate the total active users for the month up to today by summing the data from our tables. This value represents the actual user count so far.

3. Project the Entire Month’s Users

VAR CurrentMonthAvgPerDay = DIVIDE(CurrentMonthTotal, DaysElapsed)
VAR ProjectedCurrentMonthTotal = CurrentMonthAvgPerDay * DaysInMonth        

Using the daily average of active users (CurrentMonthAvgPerDay), we project the total number of users for the entire month. This is a simple projection based on how many users we’ve seen on average per day so far.

4. Incorporating Last Month’s Data

To make the projection more reliable, we bring in last month’s total active users. This provides a reference point for how user engagement looked in the previous period, offering context to our current month’s activity.

VAR PreviousMonthTotal = [_Active Users - Last Month]        

5. Gradually Adjust the Projection Over Time

VAR WeightFactor = DIVIDE(DaysElapsed, DaysInMonth)        

This WeightFactor is the core of the dynamic adjustment. Early in the month, this factor is small, meaning the projection relies heavily on last month’s data. As the days progress, the factor increases, giving more weight to the current month’s trends.

6. Calculate the Final Projection

VAR AdjustedProjection = 
    (WeightFactor * ProjectedCurrentMonthTotal) + ((1 - WeightFactor) * PreviousMonthTotal)

RETURN AdjustedProjection        

Finally, we combine the projected users for the current month with last month’s total using the WeightFactor. Early in the month, more emphasis is placed on last month’s data, while towards the end, the projection leans heavily on the current month’s performance. This smooth transition makes the projection more robust and responsive to real-time data, avoiding drastic swings early in the month.

The Result: A Dynamic, Real-Time Projection

The result of this DAX measure is a projected number of active users that starts with a heavy influence from the previous month and gradually relies more on the current month’s data as the month progresses.

This method is particularly useful in environments where user activity fluctuates daily, and you want a realistic forecast without being overly influenced by small dips or spikes early in the month. You can see this dynamic projection in action in the visual below, where the forecast updates in real-time as new user data comes in.

Improving Accuracy Through Daily Tracking

While the weighted projection method described provides a robust and flexible way to estimate active users for the month, it’s important to continually refine and evaluate your forecasting models to ensure accuracy. One effective approach to enhancing the precision of your projections is by tracking them daily.

In my own workflow, I’ve implemented a solution using Power Automate to calculate these projections on a daily basis and store them in a database. By keeping a running log of how the projections change as new data comes in each day, I can perform a detailed analysis at the end of each month to see how accurate the projections were compared to the actual number of users.

This daily tracking allows me to:

• Monitor projection trends: I can see how early in the month the forecast starts aligning with the actual results, identifying any discrepancies in the early or mid-month predictions.
• Adjust the weighting factor if necessary: By analyzing how far off the projections were at different stages of the month, I can tweak the weighting factor in future months to better reflect the reality of user behavior.
• Identify seasonal patterns or anomalies: Storing these projections month after month allows me to identify long-term trends or outliers, making it easier to adjust expectations based on predictable seasonal fluctuations or unexpected events.

Ultimately, this approach not only improves the accuracy of the forecast but also creates a feedback loop that enables constant optimization. By comparing projections with actual data month after month, I can refine the model to make increasingly accurate predictions, ensuring that decision-makers are equipped with reliable, data-driven insights as early as possible.

Conclusion: Data-Driven Forecasting with DAX

This approach to forecasting active users offers businesses a reliable method to predict performance, combining both historical and real-time data. Power BI’s DAX functionality provides flexibility and power to fine-tune these projections, ensuring that decision-makers have access to accurate, actionable insights as the month unfolds.

If your business could benefit from customized Power BI dashboards or automated data processes, I’m here to help. Check out my latest projects on FluentBrain.com and reach out if you’d like to discuss how to turn your data into actionable insights.