Specify a target vs achievement dashboard before building it

Why the spec comes first

In most sales review meetings, the first ten minutes go on arguing about the numbers rather than the business. One manager divides by calendar days, another by working days, the dashboard shows 55% achieved on the 19th and nobody knows whether that is good. A written definition of each measure ends that argument before the meeting. It also gives the analyst something to test the dashboard against.

The one-page spec

Add three lines under the table: the data source and refresh time, the as-of date shown on every page, and who owns each definition. That last line matters when the definitions are challenged later.

The query behind the tiles

The SQL below calculates every measure in the spec for three regions, using synthetic targets and daily sales. It builds a calendar for the month, counts Monday to Saturday as selling days, and takes the as-of date from a parameters table so the same query runs on any day.

-- Synthetic targets and daily sales. Selling days are Monday to Saturday.
CREATE TABLE target (region TEXT, month TEXT, target_inr INTEGER);
INSERT INTO target VALUES ('North', '2026-09', 5200000), ('West', '2026-09', 3900000),
                          ('East', '2026-09', 2600000);
CREATE TABLE sales (region TEXT, sale_date TEXT, amount_inr INTEGER);
-- North and West sell every selling day up to the as-of date; East started late.
WITH RECURSIVE d(day) AS (SELECT '2026-09-01' UNION ALL
                          SELECT date(day, '+1 day') FROM d WHERE day < '2026-09-19')
INSERT INTO sales
SELECT r.region, d.day, r.per_day
FROM d JOIN (SELECT 'North' AS region, 205000 AS per_day UNION ALL
             SELECT 'West', 128000 UNION ALL SELECT 'East', 95000) r
WHERE strftime('%w', d.day) <> '0'
  AND NOT (r.region = 'East' AND d.day < '2026-09-08');

CREATE TABLE params (as_of TEXT, month TEXT);
INSERT INTO params VALUES ('2026-09-19', '2026-09');

WITH RECURSIVE cal(day) AS (
    SELECT month || '-01' FROM params
    UNION ALL
    SELECT date(day, '+1 day') FROM cal
    WHERE date(day, '+1 day') < date((SELECT month FROM params) || '-01', '+1 month')),
days AS (
    SELECT SUM(strftime('%w', day) <> '0') AS selling_days_month,
           SUM(strftime('%w', day) <> '0' AND day <= (SELECT as_of FROM params)) AS selling_days_done
    FROM cal),
mtd AS (
    SELECT region, SUM(amount_inr) AS mtd_inr FROM sales
    WHERE sale_date <= (SELECT as_of FROM params) GROUP BY region)
SELECT t.region,
       t.target_inr,
       COALESCE(m.mtd_inr, 0)                                              AS mtd_inr,
       ROUND(100.0 * COALESCE(m.mtd_inr, 0) / t.target_inr, 1)             AS achieved_pct,
       ROUND(1.0 * t.target_inr * d.selling_days_done / d.selling_days_month) AS expected_by_now,
       ROUND(1.0 * COALESCE(m.mtd_inr, 0) / d.selling_days_done * d.selling_days_month) AS projected_month,
       ROUND(1.0 * (t.target_inr - COALESCE(m.mtd_inr, 0))
             / (d.selling_days_month - d.selling_days_done))               AS needed_per_day,
       CASE WHEN COALESCE(m.mtd_inr, 0) >= t.target_inr * d.selling_days_done / d.selling_days_month
                 THEN 'Green'
            WHEN COALESCE(m.mtd_inr, 0) >= 0.9 * t.target_inr * d.selling_days_done / d.selling_days_month
                 THEN 'Amber'
            ELSE 'Red' END                                                 AS status,
       d.selling_days_done || ' of ' || d.selling_days_month               AS selling_days
FROM target t
CROSS JOIN days d
LEFT JOIN mtd m ON m.region = t.region
WHERE t.month = (SELECT month FROM params)
ORDER BY achieved_pct DESC;

As of Saturday 19 September 2026, 17 of 26 selling days have passed.

North is at 67.0% of its monthly target against an expected 65.4% (17/26), so it is Green, and the straight-line projection is ₹53.3 lakh against a ₹52 lakh target. West has achieved ₹21.76 lakh against ₹25.5 lakh expected, about 85% of pace, which is Red under the 90% rule. East looks worst at 40.2%.

The projection trap East shows

East's projection of ₹15.98 lakh assumes the region sold at its average rate across all 17 elapsed days. In the synthetic data it only started invoicing on 8 September, so it has actually been selling ₹95,000 a day over 11 days. Nine more selling days at that rate would take it to about ₹19 lakh. The straight-line figure is still the right default, because it needs no judgement, but the spec should say how to treat a region that started late, a large single order, or a stock-out week. The simplest rule is a note in the tile, not a different formula for one region.

The same caution applies at the start of the month. On the 2nd, a projection built from one day of sales can swing wildly. Many teams hide the projection until five selling days have passed; write that rule into the spec if you adopt it.

Selling days, holidays and the financial year

Counting selling days is where Indian calendars need care. Sundays are excluded here. If branches close for regional holidays such as Diwali, Pongal or Onam, keep a holiday table by state and exclude those dates for the affected regions, or the expected target runs ahead of what was possible. Monthly targets are normally set inside the April–March financial year, so the quarterly and annual versions of this dashboard should sum April–June as Q1 and so on; the calendar logic is covered in add Indian financial year and quarter columns.

Building it in Power BI

The same measures translate directly into DAX, given a date table with IsSellingDay and YearMonth columns, and a report filtered to the as-of date:

MTD Sales = CALCULATE ( SUM ( Sales[Amount] ), DATESMTD ( 'Date'[Date] ) )

Selling Days Done =
CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[IsSellingDay] = TRUE (),
            DATESMTD ( 'Date'[Date] ) )

Selling Days Month =
VAR ThisMonth = MAX ( 'Date'[YearMonth] )
RETURN
    CALCULATE ( COUNTROWS ( 'Date' ), REMOVEFILTERS ( 'Date' ),
                'Date'[YearMonth] = ThisMonth, 'Date'[IsSellingDay] = TRUE () )

Expected By Now = [Target] * DIVIDE ( [Selling Days Done], [Selling Days Month] )

Status =
SWITCH ( TRUE (),
    [MTD Sales] >= [Expected By Now], "Green",
    [MTD Sales] >= 0.9 * [Expected By Now], "Amber",
    "Red" )

Test the measures against the SQL output for the same day before publishing. If the two disagree, one of them has a different definition of a selling day.

Layout that follows the spec

Put the as-of date and selling-days count at the top left, where every reader looks first. Show one row per region with MTD, achieved %, expected by now and status side by side, so the comparison that drives the colour is visible. Put projected month-end and needed per day in a second group, labelled as estimates. Sort by gap to expected, not alphabetically, so the region that needs attention is at the top.

Writing a measure definition that survives a sales review is as much a part of dashboard work as the visuals. Our Data Analyst programme treats the spec, the SQL and the Power BI build as one piece of work.