Datesinperiod and DateBetween Function – DAX Power BI

In DAX (Data Analysis Expressions) for Power BI, the DATESINPERIOD and DATESBETWEEN functions are used to create dynamic date ranges for time-based calculations. Here’s a detailed look at each function and how they can be used.

DATESINPERIOD

The DATESINPERIOD function returns a table containing a contiguous set of dates, shifting backwards or forwards based on a specified interval. It’s often used for rolling period calculations like rolling months or years.

Syntax

DAX

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

• <dates>: A column containing date values.
• <start_date>: The date that determines the period’s start date.
• <number_of_intervals>: The number of intervals to shift from the start date. This can be positive (future) or negative (past).
• <interval>: The interval to use. It can be DAY, MONTH, QUARTER, or YEAR.

Example: Last 12 Months Sales

To calculate sales over the last 12 months, you can use:

DAX

Last12MonthsSales = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH)
)

In this measure:

• SUM(Sales[SalesAmount]) calculates the total sales amount.
• DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH) shifts the date range by 12 months from the maximum date in the current context.

DATESBETWEEN

The DATESBETWEEN function returns a table containing a set of dates between a specified start and end date. This function is useful for creating custom date ranges.

Syntax

DAX

DATESBETWEEN(<dates>, <start_date>, <end_date>)

• <dates>: A column containing date values.
• <start_date>: The start date of the period.
• <end_date>: The end date of the period.

Example: Sales Between Two Dates

To calculate sales between January 1, 2024, and March 31, 2024, you can use:

DAX

SalesBetweenDates = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    DATESBETWEEN(DateTable[Date], DATE(2024, 1, 1), DATE(2024, 3, 31))
)

In this measure:

• SUM(Sales[SalesAmount]) calculates the total sales amount.
• DATESBETWEEN(DateTable[Date], DATE(2024, 1, 1), DATE(2024, 3, 31)) creates a date range from January 1, 2024, to March 31, 2024.

Comparison and Use Cases

• DATESINPERIOD: Use this function when you need a dynamic, rolling period based on a specific start date and an interval (e.g., last 7 days, last 3 months).
• DATESBETWEEN: Use this function when you need to define a fixed date range with specific start and end dates (e.g., a specific quarter, a custom fiscal period).

Practical Implementation in Power BI

1. Load Data: Ensure your data (both Sales and DateTable) is loaded into Power BI.
2. Create Date Table: If you don’t have a DateTable, create one using:DAX

1. DateTable = CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate]))
2. Establish Relationships: Create a relationship between the OrderDate column in the Sales table and the Date column in the DateTable.
3. Create Measures: Add measures using the DATESINPERIOD and DATESBETWEEN functions based on your analysis needs.

Example Visualization

• Table: Create a table with columns OrderDate, SalesAmount, Last12MonthsSales, and SalesBetweenDates to compare current sales with the calculated periods.
• Line Chart: Plot OrderDate on the x-axis and the SalesAmount, Last12MonthsSales, and SalesBetweenDates measures on the y-axis to visualize trends over time.

Example Measures in Context

Last 12 Months Sales Measure

DAX

Last12MonthsSales = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH)
)

Sales Between Specific Dates Measure

DAX

SalesBetweenDates = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    DATESBETWEEN(DateTable[Date], DATE(2024, 1, 1), DATE(2024, 3, 31))
)

By using these functions, you can perform powerful time-based analyses in Power BI, providing insights into rolling periods or specific date ranges as required by your business scenarios.