DATEADD (Period Shifts)

Power BI · Advanced DAX & Time Intelligence

Theory

DATEADD(dates, number_of_intervals, interval) shifts the current set of dates by a number of days, months, quarters or years, forwards (positive) or backwards (negative). Inside CALCULATE it lets you compare any period with an earlier or later one: DATEADD('Date'[Date], -1, MONTH) gives the previous month, -1 QUARTER the previous quarter, and -1 YEAR the same as SAMEPERIODLASTYEAR.

For month-over-month growth on 2024 data: January 2024 (₹1,200) is compared with December 2023 (₹1,400), a drop of 14.3%; February (₹2,300) with January, up 91.7%; March (₹2,200) with February, down 4.3%; and April (₹500) with March, down 77.3%. Note that January needs December 2023 from the previous year, which works because filters on Year are replaced by the shifted dates, provided the date table includes 2023 and is marked as a date table. DATEADD requires the dates column of a proper, contiguous date table.

Why we use it

Business reviews compare with the previous month or quarter as often as with last year. DATEADD covers every such shift with one function and one pattern.

When to use it

Use DATEADD for period-over-period comparisons of any length. Use SAMEPERIODLASTYEAR for readability when the shift is exactly one year, and PREVIOUSMONTH when you always want the whole previous month regardless of the dates in context.

How it works

  1. Visual cell: Feb 2024
  2. Read the current dates in context
  3. DATEADD shifts them back one month (Jan 2024)
  4. CALCULATE evaluates Total Sales on the shifted dates
  5. Does the shifted period exist in the date table? Yes: Return its value and compute MoM %. No: Return blank.
  6. Period-over-period comparison

Syntax

DATEADD(<dates>, <number_of_intervals>, <interval>)
interval: DAY | MONTH | QUARTER | YEAR
Prev Month Sales = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))

Example

Prev Month Sales = 
CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, MONTH)
)

MoM % = DIVIDE([Total Sales] - [Prev Month Sales], [Prev Month Sales])
// Jan 2024: (1,200 - 1,400) / 1,400 = -14.3%

Prev Quarter Sales = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, QUARTER))

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic