Comparing Year-To-Date Financial Data Across Years
It is July and leadership wants to know: how does this year compare to last year? Not full-year totals, because the year is not over. They want a fair comparison -- January through June 2024 versus January through June 2023 -- broken down by product category. Which categories are growing? Which are declining? By how much?
This is a year-over-year (YoY) comparison, and it normally requires a SQL query with self-joins or a BI tool with calculated fields. In ExploreMyData, you can build it with a multi-view pipeline. Here is how.
What you need
An orders or transactions file spanning at least two calendar years. You need columns for order_date, category (or product, region, etc.), and revenue (or whatever metric you are comparing).
Step 1: Extract year and month
Open the toolbar and select Extract Date from the Transform group. Apply it twice:
- Extract
yearfromorder_date→ name itorder_year - Extract
monthfromorder_date→ name itorder_month
Now every row has a year number and a month number. This is what makes the comparison possible -- you can match January 2024 to January 2023 by joining on the month number.
| order_id | order_date | category | revenue | order_year | order_month |
|---|---|---|---|---|---|
| ORD-2210 | 2023-02-14 | Software | 4,200.00 | 2023 | 2 |
| ORD-4801 | 2024-02-09 | Software | 5,100.00 | 2024 | 2 |
| ORD-4802 | 2024-03-22 | Hardware | 8,750.00 | 2024 | 3 |
Extract Date stamps every row with the numeric year and month - the join key that lets you match January 2024 to January 2023.
Step 2: Filter to the comparison months
Select Filter from the Filter & Sort group:
- Column:
order_month - Operator: less than or equal to
- Value:
6
This keeps only January through June for both years. The comparison is now fair: you are comparing the same months, not a full year against a partial year. Adjust the cutoff month based on how much of the current year has elapsed.
Step 3: Create two views
This is where ExploreMyData's multi-view feature comes in. From the filtered dataset, create two separate views:
View A (this year): Add a Filter step where order_year = 2024. Then add a Group By step: group by category and order_month, and aggregate SUM(revenue). Name the sum column revenue_2024.
View B (last year): Same steps but filter where order_year = 2023 and name the sum column revenue_2023.
Each view now has one row per category per month with the total revenue for that year.
View A - 2024 (Jan–Jun), grouped by category + month
| category | order_month | revenue_2024 |
|---|---|---|
| Software | 1 | 62,400.00 |
| Hardware | 1 | 41,800.00 |
| Services | 1 | 28,950.00 |
View B - 2023 (Jan–Jun), grouped by category + month
| category | order_month | revenue_2023 |
|---|---|---|
| Software | 1 | 54,100.00 |
| Hardware | 1 | 45,200.00 |
| Services | 1 | 24,300.00 |
Same shape, different year. The join step combines them into one row per category per month with both revenue columns side by side.
Step 4: Join the two years together
Select Join from the Combine group. Configure it:
- Left view: View A (2024)
- Right view: View B (2023)
- Join type: Inner (or Left, if you want to see categories that exist in 2024 but not 2023)
- Join on:
category=categoryANDorder_month=order_month
The result is a table with one row per category per month, with both revenue_2024 and revenue_2023 columns side by side.
Step 5: Calculate the year-over-year change
Select Math from the Transform group. Create a new column with the formula:
(revenue_2024 - revenue_2023) / revenue_2023 * 100
Name it yoy_change_pct. A value of 15 means 15% growth. A value of -8 means an 8% decline.
If you also want the absolute dollar change, add another Math column: revenue_2024 - revenue_2023 and name it yoy_change_abs.
Step 6: Label the growth direction
Select Add Column from the Columns group. Use a conditional expression to create a human-readable label:
- If
yoy_change_pct > 2→ "Growing" - If
yoy_change_pct < -2→ "Declining" - Otherwise → "Flat"
Name it trend. The 2% threshold avoids labeling minor fluctuations as meaningful trends. Adjust the threshold to whatever makes sense for your business.
| category | order_month | revenue_2024 | revenue_2023 | yoy_change_pct | trend |
|---|---|---|---|---|---|
| Software | 1 | 62,400.00 | 54,100.00 | 15.3 | Growing |
| Hardware | 1 | 41,800.00 | 45,200.00 | -7.5 | Declining |
| Services | 1 | 28,950.00 | 24,300.00 | 19.1 | Growing |
| Training | 1 | 9,100.00 | 8,950.00 | 1.7 | Flat |
Software and Services are growing; Hardware is declining 7.5% YoY. Sort by yoy_change_pct to rank categories from fastest growth to steepest decline.
Reading the results
Sort by yoy_change_pct descending to see your fastest-growing categories at the top. Sort ascending to see the biggest declines. Filter to trend = 'Declining' to focus on problem areas.
For a summary view, you can add a Group By at the end: group by category, sum revenue_2024 and revenue_2023, then recalculate the YoY change on the totals. This gives you one row per category with the full H1 comparison.
The full pipeline
- Extract Date - year and month from order_date
- Filter - months 1 through 6 only
- Two views - one filtered to 2024, one to 2023, each grouped by category + month
- Join - on category and order_month
- Math - (revenue_2024 - revenue_2023) / revenue_2023 * 100
- Add Column - trend label based on change threshold
Next quarter, update the month filter to include July through September and the pipeline recalculates everything. Swap in a different file with different categories and it still works. The pipeline does not care about the specific data -- it cares about the structure.