Why year-over-year comparison matters

Absolute numbers tell you what happened. Year-over-year comparison tells you whether what happened is good or bad. A business that made 10,000 bookings this month cannot evaluate that number without knowing that last year it made 6,000 — or 15,000. Context is everything in business reporting, and year-over-year comparison is the most fundamental context any dashboard can provide.

In a travel platform context, the key comparison dimensions are bookings volume, business revenue, room nights, and agent activity — all measured against the same period last year and the year before that. This three-year comparison (current year YTD, last year to date, last-to-last year to date) gives leadership a trend rather than a snapshot, which is what drives meaningful decisions.

The business value: A booking growth of 176% YTD versus LYTD means something very different when you know that LYTD itself was down 45% versus LLYTD. Three years of data in one view prevents misleading conclusions from single-period comparisons.

The terminology: YTD, LYTD, LLYTD

YTD stands for Year to Date — the cumulative total from the start of the current calendar or fiscal year up to today, or up to the latest date in the data. This is your current performance baseline.

LYTD stands for Last Year to Date — the cumulative total for the same period in the previous year. If today is June 15th, LYTD is the total from January 1st to June 15th of last year, not the full previous year. This is a critical distinction: comparing YTD to a full previous year total produces misleading growth percentages.

LLYTD stands for Last to Last Year to Date — the same period two years ago. Including this third comparison point reveals whether growth is accelerating, decelerating, or recovering from an anomalous year.

Prerequisites: the date table

DAX time intelligence functions — TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, and others — require a properly configured date table to work correctly. Without one, these functions either fail silently or produce incorrect results.

A correct date table must meet three requirements. First, it must contain a continuous sequence of dates with no gaps — every single calendar date in the range your data covers. Second, it must be marked as a Date Table in Power BI (right-click the table in the Model view and select Mark as date table, then specify the date column). Third, it must be connected to your fact table through a relationship on the date field.

The date table should also contain calculated columns that your reports will need: Year, Month, MonthNo, Quarter, QuarterNo, DayOfWeek, DayOfWeekNo, and any fiscal period columns relevant to your business. Building these in the date table once means you never have to write them in DAX measures.

Writing your first YTD measure

The simplest YTD booking count measure uses TOTALYTD, a built-in time intelligence function that calculates a year-to-date total automatically:

The DAX formula bar in Power BI Desktop — where you create new measures using the New Measure button on the ribbon.

YTD Bookings =
TOTALYTD(
  DISTINCTCOUNT(BookingsData[BookingID]),
  DateTable[Date]
)

TOTALYTD takes two required arguments: the expression to calculate, and the date column from your marked date table. It automatically accumulates from the start of the year to the current date context — whatever date or period is selected in the report.

For booking revenue rather than count, replace DISTINCTCOUNT with SUM:

YTD Business =
TOTALYTD(
  SUM(BookingsData[BookingAmountINR]),
  DateTable[Date]
)

Writing LYTD and LLYTD measures

LYTD requires shifting the date context back exactly one year while preserving the year-to-date boundary. The cleanest approach uses CALCULATE with SAMEPERIODLASTYEAR:

LYTD Bookings =
CALCULATE(
  [YTD Bookings],
  SAMEPERIODLASTYEAR(DateTable[Date])
)

Notice that LYTD Bookings references the YTD Bookings measure rather than rewriting the DISTINCTCOUNT. This is a deliberate pattern — building measures on top of other measures keeps your logic DRY (Don't Repeat Yourself) and means that if you ever change the base calculation, all dependent measures update automatically.

For LLYTD, shift back two years using DATEADD:

LLYTD Bookings =
CALCULATE(
  [YTD Bookings],
  DATEADD(DateTable[Date], -2, YEAR)
)
YTDCurrent year to date
LYTDSame period last year
LLYTDSame period 2 years ago

Calculating growth percentage

Once you have YTD, LYTD, and LLYTD measures, growth percentage is straightforward. The pattern is (Current - Previous) / Previous, expressed as a percentage:

Booking Growth YTD vs LYTD =
DIVIDE(
  [YTD Bookings] - [LYTD Bookings],
  [LYTD Bookings],
  0 -- returns 0 instead of error when LYTD is blank
)

Always use DIVIDE rather than the division operator (/) for percentage calculations. DIVIDE handles division by zero gracefully — the third argument specifies what to return when the denominator is zero or blank, preventing the dreaded blank or infinity result that breaks visuals.

The CALCULATE function explained

CALCULATE is the most important function in DAX. It appears in almost every intermediate or advanced measure, and understanding what it does conceptually unlocks the rest of the language.

CALCULATE evaluates an expression in a modified filter context. The first argument is the expression — any DAX measure or aggregate. The remaining arguments are filters that modify the context in which that expression is evaluated.

When you write CALCULATE([YTD Bookings], SAMEPERIODLASTYEAR(DateTable[Date])), you are saying: evaluate the YTD Bookings measure, but replace the current date filter context with the equivalent dates from last year. The measure logic stays the same; the dates it operates on change.

The mental model for CALCULATE: Think of it as a context switcher. The first argument is the calculation. Everything after it modifies the world in which that calculation runs — changing which rows are visible, which dates are in scope, which categories are included. Master this mental model and most DAX measures become readable.

Common errors and how to fix them

The most common error is LYTD returning the same value as YTD. This almost always means the date table is not marked as a Date Table, or the relationship between the date table and the fact table is not active. Check both in Model view before debugging the measure itself.

The second common error is LYTD returning a full previous year total rather than a year-to-date total. This happens when you use PREVIOUSYEAR instead of SAMEPERIODLASTYEAR. PREVIOUSYEAR returns the complete prior calendar year. SAMEPERIODLASTYEAR returns the matching period — the correct function for YTD comparisons.

The third error is growth percentage showing as blank when LYTD is zero or blank. This is the division-by-zero case — fix it by using DIVIDE with a third argument of zero or BLANK() depending on your display preference.

Putting it all together

A complete set of year-over-year measures for a travel platform dashboard would include: YTD Bookings, LYTD Bookings, LLYTD Bookings, YTD Business, LYTD Business, LLYTD Business, Booking Growth YTD vs LYTD, Booking Growth YTD vs LLYTD, Business Growth YTD vs LYTD, and Business Growth YTD vs LLYTD. That is ten measures — but because each one builds on the previous, the actual logic written is minimal.

These measures power the core comparison tables and bar-with-line charts that form the backbone of any business summary dashboard. Branch-wise, agent-wise, supplier-wise, destination-wise — every dimensional slice uses the same set of measures, just filtered by the dimension in context.

In the next post, we cover slicers — the filtering mechanism that makes all of these measures interactive and gives business users the ability to explore the data themselves without changing the underlying report.

`