DATEADD for period shifts in Power BI - Dashboard Guide

Dashboard Mode - DATEADD for period shifts

Business Question

How can we compare sales this year to sales in the same period last year using period shifts?

Sample Data

Date	Sales Amount
2023-01-01	100
2023-02-01	150
2023-03-01	200
2023-04-01	250
2023-05-01	300
2023-06-01	350
2023-07-01	400

Dashboard Components

• KPI Card: Total Sales This Year
  Formula: Total Sales = SUM('Sales'[Sales Amount])
  Result: 1750
• KPI Card: Total Sales Same Period Last Year
  Formula: Sales Last Year = CALCULATE(SUM('Sales'[Sales Amount]), DATEADD('Sales'[Date], -1, YEAR))
  Result: 0 (No data for 2022 in sample)
• Line Chart: Monthly Sales This Year
  X-axis: 'Sales'[Date]
  Y-axis: Total Sales measure
• Line Chart: Monthly Sales Last Year
  X-axis: 'Sales'[Date]
  Y-axis: Sales Last Year measure
• Table: Sales by Month
  Columns: Date, Sales Amount, Sales Last Year

Dashboard Layout

+-----------------------------+-----------------------------+
| Total Sales This Year (KPI) | Sales Last Year (KPI)       |
+-----------------------------+-----------------------------+
|                             Line Charts:                          |
| +-------------------------+ +-----------------------------+    |
| | Monthly Sales This Year | | Monthly Sales Last Year      |    |
| +-------------------------+ +-----------------------------+    |
+---------------------------------------------------------------+
| Table: Sales by Month                                          |
+---------------------------------------------------------------+

Interactivity

A date slicer allows selecting a date range. When the user changes the date range, all components update to show sales and last year sales for the selected period.

The DATEADD function shifts the date context by -1 year to calculate last year's sales for the same period.

Self Check

If you add a filter to show only dates from January to March 2023, which components update?

• Both KPI cards update to show total sales and last year sales for Jan-Mar.
• Both line charts update to show monthly sales and last year monthly sales for Jan-Mar.
• The table updates to show only rows for Jan-Mar with sales and last year sales.

Key Result

Dashboard compares current year sales to last year using DATEADD for period shifts.