Advanced Time-Based Comparisons

Year-over-Year Growth

Year-over-year (YoY) growth compares a value with the corresponding period in the previous year.

First, create a measure for the previous year's sales:

Sales Previous Year =

CALCULATE(

[Total Sales],

SAMEPERIODLASTYEAR(Calendar[Date])

)

Then calculate the percentage change:

Year-over-Year Growth =

DIVIDE(

[Total Sales] - [Sales Previous Year],

[Sales Previous Year]

)

Format the measure as a percentage.

For example, if sales increase from ₹100,000 to ₹120,000, the growth rate is 20%.

If the previous year's value is zero or blank, DIVIDE returns blank by default because no alternate result was supplied.

Quarter-to-Date Sales

Quarter-to-date (QTD) sales calculates cumulative sales from the beginning of the current quarter through the current date context.

Sales QTD =

TOTALQTD(

[Total Sales],

Calendar[Date]

)

This measure is useful for tracking performance during the current quarter.

For organizations that use a non-calendar financial year, ensure that the relevant date table and calculation settings reflect the required fiscal calendar.

Moving Averages

A moving average smooths short-term fluctuations and helps identify broader trends.

For example, a rolling three-month average can compare each month's sales with sales over a three-month window.

One possible pattern is:

Rolling 3 Month Sales =

CALCULATE(

[Total Sales],

DATESINPERIOD(

Calendar[Date],

MAX(Calendar[Date]),

-3,

MONTH

)

)

This expression calculates sales over a three-month date interval ending at the maximum date in the current context. Depending on the visual and date context, the interval may include part of the current month rather than exactly three complete calendar months.

To calculate a rolling average, divide the sales over the intended window by the appropriate number of months, taking care to handle incomplete periods and missing data correctly.

Moving averages are useful for monitoring sales trends, demand patterns, and performance changes over time.