No More Confusion Between DATEADD, DATESINBETWEEN, and DATESINPERIOD

Working with dates in Power BI can sometimes be confusing, especially when it comes to choosing between DATEADD, DATESINBETWEEN, and DATESINPERIOD.
 All three deal with date ranges, but each one serves a different purpose.

 Let’s make it super simple to understand

DATEADD moves, DATESINBETWEEN limits, DATESINPERIOD rolls

1 - DATEADD, Shifts Dates Forward or Backward

Purpose:
 Used to move dates forward or backward by a specific time interval — like days, months, quarters, or years.

Example:

DATEADD(Dates[Date], -1, MONTH) 

This moves the date one month backward from the current context.

Use Case:
 Compare this month’s sales with last month’s sales or previous year’s data.

Key Notes:

•      Needs a continuous date column.            
•      Works great for period-over-period comparison (Month-over-Month, Year-over-Year).      
•      Very useful in trend analysis.      

2 - DATESINBETWEEN, Returns All Dates Between Two Dates

Purpose:
 Returns all dates between a start date and an end date, inclusive.

Example:

DATESINBETWEEN(Dates[Date], DATE(2024,1,1), DATE(2024,3,31)) 

Gives all dates from 1st Jan 2024 to 31st Mar 2024.

Use Case:
 Perfect when users select a custom date range (like a specific quarter or campaign period).

Key Notes:

•      Very flexible and straightforward.           
•      Works well with user-selected filters.     
•      Commonly used in dynamic reports and dashboards.      

3 - DATESINPERIOD, Returns Dates in a Rolling Period

Purpose:
 Returns a set of dates starting from a date and moving backward or forward by a fixed time period.

Example:

DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -3, MONTH) 

Returns all dates for the last 3 months ending at the latest date in the data.

Use Case:
 Ideal for rolling periods, like:

•      Last 3 months    
•      Last 30 days       
•      Year-to-Date (YTD)          

Key Notes:

•      Excellent for rolling average or moving total calculations.             
•      Handles dynamic time windows easily.  
•      Works great in trend and performance reports.