SolveWithExcel

Excel Challenge #17: Year-to-Date Sales Dashboard (YTD Total + YTD by Region + YTD Trend)

Difficulty

Intermediate–Advanced

Estimated Time

55–75 minutes

The Problem

Rolling windows show momentum.
But leadership also needs Year-to-Date reporting:

  • “How much have we sold so far this year?”
  • “Which region is leading YTD?”
  • “How does this year compare to last year (same period)?”

In this challenge, you’ll build a YTD dashboard that updates automatically based on an As-Of Date.

Dataset

Worksheet: SalesData

| Date | Region | Product | Sales |

(Reuse the SalesData from Challenge #16.)

Your Task

Part A — Controls (As-Of Date + Year Start)

On the Dashboard sheet:

  1. As-Of Date (B2)
  • Default to latest date in SalesData (formula)
  1. Year Start (B3)
  • Use a formula that returns January 1 of the As-Of Date’s year

Example logic:

  • Year Start = DATE(YEAR(AsOf),1,1)

Part B — YTD Total Sales

Calculate YTD sales using inclusive date filtering:

  • Date >= Year Start
  • Date <= As-Of Date

Use SUMIFS.

Part C — YTD Sales by Region

Build a table:

RegionYTD Sales
North
South
East
West

Each value must be YTD-filtered.

Part D — Compare to Last Year (Same Period)

Calculate:

  1. LYTD Total (Last Year To Date)
  • Same-day cutoff, but one year earlier
  1. YTD Delta = YTD − LYTD
  2. YTD Delta % = Delta / LYTD

Part E — YTD Trend + Top Region Callouts

Add callouts:

  • Trend: Up / Down / Flat (based on YTD Delta)
  • Top Region (YTD): highest YTD regional sales

Requirements

  • Formulas only (SUMIFS, DATE, YEAR, EDATE, MAX, XLOOKUP, IF)
  • No Pivot Tables
  • Must update if As-Of Date changes
  • Date filtering must be inclusive

Learning Objectives

By completing this challenge, you will learn how to:

  • Build YTD logic from an As-Of Date
  • Compare current YTD to last-year same-period performance
  • Create executive-style YTD KPIs and callouts
  • Use date functions to automate time intelligence in Excel

Next Challenge

Excel Challenge #18:

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical Excel challenges?
Subscribe to the Solve With Excel newsletter and get new problems delivered to your inbox.