The Financial Analyst’s Toolkit: MTD, YTD, and Same-Period-Last-Year in SQL

By Harsh Gupta

In the world of sales and finance, reporting raw totals is meaningless. What truly matters is context. You need to know if $1 million in sales is good or bad, and that requires comparison: to a budget, to last month, or, most critically, to the same period last year.

While BI tools like Power BI and Tableau have built-in time intelligence, mastering the SQL for these calculations is non-negotiable. It ensures your data pipelines are accurate, efficient, and reliable before it even hits the dashboard.

Get Harsh Gupta’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This guide breaks down the precise SQL logic needed for the three most essential time-series metrics.

Database Note

The date functions below (e.g., DATE_TRUNC, DATE_ADD) use syntax common to modern analytical databases (PostgreSQL, BigQuery, Snowflake). You may need to substitute them with platform-specific functions (e.g., DATEFROMPARTS in SQL Server, TRUNC in Oracle).

1. Calculating Year-to-Date (YTD) Sales

YTD sales measure cumulative performance from the first day of the current fiscal year up to the latest date in your data.

• The Goal: Find the sum of sales from January 1st of the current year through today.
• The SQL Solution:

SQL

SELECT
    SUM(sales_amount) AS YTD_Sales
FROM
    sales
WHERE
    -- Filter 1: Date must be in the current calendar year
    EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE())
    -- Filter 2: Date must be up to and including the current day
    AND order_date <= CURRENT_DATE();

2. Calculating Month-to-Date (MTD) Sales

MTD sales measure cumulative performance from the first day of the current calendar month up to the latest date.

• The Goal: Find the sum of sales from the 1st of the current month through today.
• The SQL Solution:

SQL

SELECT
    SUM(sales_amount) AS MTD_Sales
FROM
    sales
WHERE
    -- Filter 1: Date must be in the current calendar month
    DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE())
    -- Filter 2: Date must be up to and including the current day
    AND order_date <= CURRENT_DATE();

• Alternative Logic: You can replace DATE_TRUNC with separate EXTRACT(YEAR) and EXTRACT(MONTH) filters if your database doesn't support DATE_TRUNC.

3. Calculating Same-Period-Last-Year (SPLY) Sales

This is the most critical and complex calculation. You need to compare this year’s YTD/MTD sales against last year’s sales, but only for the exact same date range.

• The Goal: Find sales from January 1st of last year up to October 5th of last year.
• The SQL Solution (using CTEs for clarity):

SQL

WITH DateRanges AS (
    SELECT
        -- Current YTD range
        DATE_TRUNC('year', CURRENT_DATE()) AS YTD_Start,
        CURRENT_DATE() AS YTD_End,
        -- SPLY range (simply shift the YTD range back one year)
        DATE_ADD('year', -1, DATE_TRUNC('year', CURRENT_DATE())) AS SPLY_Start,
        DATE_ADD('year', -1, CURRENT_DATE()) AS SPLY_End
)
SELECT
    'Current Year' AS period,
    SUM(CASE WHEN order_date BETWEEN r.YTD_Start AND r.YTD_End THEN sales_amount END) AS Sales
FROM
    sales, DateRanges r

UNION ALL

SELECT
    'Last Year' AS period,
    SUM(CASE WHEN order_date BETWEEN r.SPLY_Start AND r.SPLY_End THEN sales_amount END) AS Sales
FROM
    sales, DateRanges r;

• Why this works: The DateRanges CTE calculates the precise start and end dates for both the current period and the last-year period using date arithmetic (DATE_ADD with -1 year). The UNION ALL then runs the sales calculation for both date ranges and stacks them into a single comparison table.

Financial Analyst’s SQL Cheat Sheet

This infographic summarizes the key date functions needed for each calculation.

Conclusion: Beyond the Dashboard

Mastering these SQL calculations makes you a truly versatile analyst. While Power BI’s DAX provides a high-level solution, writing the SQL yourself ensures you control the logic, handle non-standard fiscal calendars, and build robust data pipelines that feed any destination.

What is your preferred method for handling tricky date calculations like SPLY in your specific database? Share your SQL tips below!

Harsh Gupta

LinkedIn