Schema Theory

← Back to KPIs & Dashboards

Sales vs. Last Year Same Weekday

Description

Shows daily sales compared to the same weekday one year ago (exactly 364 days prior), helping identify trends aligned by weekday behavior rather than calendar date.

Visual Example

Sales vs Last Year Visual

DAX Formulas

Sales LY = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    DATEADD('Date'[Date], -364, DAY)
)
Change vs Last Year (%) = 
VAR CurrSales = SUM(Sales[SalesAmount])
RETURN
IF(
    ISBLANK(Sales[Sales LY]) || Sales[Sales LY] = 0,
    BLANK(),
    DIVIDE(CurrSales - Sales[Sales LY], Sales[Sales LY])
)

SQL Examples

-- Daily comparison joined on same weekday last year
SELECT
  curr.OrderDate AS Date,
  curr.TotalSales AS SalesThisYear,
  prev.TotalSales AS SalesLY,
  CASE 
    WHEN prev.TotalSales = 0 OR prev.TotalSales IS NULL THEN NULL
    ELSE (curr.TotalSales - prev.TotalSales) / prev.TotalSales
  END AS ChangeVsLY
FROM
  SalesSummary curr
LEFT JOIN SalesSummary prev
  ON prev.OrderDate = DATEADD(DAY, -364, curr.OrderDate);
-- Using window function:
SELECT
  OrderDate,
  TotalSales,
  LAG(TotalSales, 364) OVER (ORDER BY OrderDate) AS SalesLY,
  CASE 
    WHEN LAG(TotalSales, 364) OVER (ORDER BY OrderDate) = 0 
         OR LAG(TotalSales, 364) OVER (ORDER BY OrderDate) IS NULL THEN NULL
    ELSE (TotalSales - LAG(TotalSales, 364) OVER (ORDER BY OrderDate)) 
         / LAG(TotalSales, 364) OVER (ORDER BY OrderDate)
  END AS ChangeVsLY
FROM SalesSummary;

Usage Notes

Downloads