I am building a financial tracking dashboard and need a way to project our final month-end Gross Profit (GP) percentage. Specifically, I want to calculate the expected GP% by combining our actuals to date with a projection for the remaining working days based on our current daily run rate. How can I dynamically count the remaining business days (excluding weekends/holidays) and use that to forecast total month-end revenue and COGS to derive the expected margin?
3 answers
To achieve this, you need a robust Date table with a Working Day flag. First, calculate your daily run rate for Revenue and COGS by dividing MTD actuals by the number of working days passed. Next, calculate the remaining working days using: COUNTROWS(FILTER('Date', 'Date'[Date] > TODAY() && 'Date'[Date] <= EOMONTH(TODAY(), 0) && 'Date'[IsWorkingDay] = 1)). You can then forecast the total month-end values: Projected Revenue = [Actual Revenue] + ([Daily Revenue Run Rate] * [Remaining Working Days]). Do the same for COGS, then your expected GP% is simply DIVIDE([Projected Revenue] - [Projected COGS], [Projected Revenue]). This ensures your forecast stays grounded in current performance while accounting for the specific number of billing days left in the calendar.
That run-rate logic assumes the rest of the month will perform exactly like the first half. Is there a way to weight this calculation so that the "remaining days" forecast is based on a longer historical average, like the last 3 months, instead of just the current MTD performance which might be skewed by a single large mid-month deal
Using the NETWORKDAYS function in DAX is the fastest way to get your day counts now. It handles the weekend logic automatically without needing a complex filter string.
Great suggestion, Patricia. I’ve started using NETWORKDAYS for all my pacing reports. I’d also add that you should include a "Holidays" table in that function’s parameters. If you have a bank holiday coming up, the projection will naturally adjust downwards because it knows there’s one less day to generate margin. This level of detail is what makes a dashboard actually useful for sales leadership during the final week of the quarter.
You're absolutely right to be cautious about skewing, Thomas. To fix this, you can swap the [Daily Run Rate] variable with a 3-month rolling average. Use AVERAGEX(DATESINPERIOD('Date'[Date], TODAY(), -3, MONTH), [Daily Sales]) to get a more stable baseline. When you multiply this "smoothed" rate by your remaining working days, your month-end projection becomes much more resilient to one-off spikes. It’s a standard practice in enterprise Data Science to use these moving averages for more reliable budgetary forecasting.