Sign In
Get Started

DATESINPERIOD Function in DAX – A Detailed Guide

by | Power BI

Introduction to DATESINPERIOD in DAX

Purpose

The DATESINPERIOD function is used to return a table that contains a column of dates that begins with a specified start date and continues for the specified interval. This function is often used in the context of calculating time-based measures like rolling periods, moving averages, and cumulative totals.

Syntax

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)
  • dates: A column containing dates.
  • start_date: The start date as a filter context.
  • number_of_intervals: The size of the interval.
  • interval: The interval unit. This can be DAY, MONTH, QUARTER, or YEAR.

Example Implementation

Scenario

Calculate the sum of sales over the last 3 months from a specific date.

Sales Now On Advertisement

Data Model

We assume the following tables:

  1. Sales
    • Sales[Date]: The date of each sale.
    • Sales[Amount]: The amount of each sale.
  2. Calendar
    • Calendar[Date]: A continuous range of dates.

Sample Data

Sales Table
------------
| Date       | Amount |
|------------|--------|
| 2023-01-10 | 500    |
| 2023-02-14 | 700    |
| 2023-03-05 | 300    |
| 2023-03-27 | 400    |
| 2023-04-06 | 1000   |

Calendar Table
---------------
| Date       |
|------------|
| 2023-01-01 |
| 2023-01-02 |
| ...        |
| 2023-04-30 |

DAX Calculation

SalesLast3Months :=
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD(
        Calendar[Date],
        MAX(Sales[Date]),
        -3,
        MONTH
    )
)

Explanation

  1. SUM(Sales[Amount]): Aggregates total sales.
  2. DATESINPERIOD(…, MAX(Sales[Date]), -3, MONTH):
    • Calendar[Date]: The date column from the Calendar table.
    • MAX(Sales[Date]): The latest date from the Sales table in the current filter context.
    • -3: Goes back 3 intervals (months) from the MAX(Sales[Date]).
    • MONTH: Specifies the interval as months.

This DAX expression sums up the sales amounts for the 3-month period ending at the latest sales date in the current filter context.

Conclusion

The DATESINPERIOD function in DAX is a powerful feature for time-based calculations. By specifying a date column, a starting point, an interval size, and the type of interval, you can create dynamic and insightful time-period analyses. This foundational understanding equips you to explore more complex scenarios with confidence.

Practical Examples and Use Cases of DAX DATESINPERIOD Function

The DATESINPERIOD function in Data Analysis Expressions (DAX) is used to return a table of dates in a specified period. This function can be extremely powerful for time-based calculations in Power BI, SSAS Tabular, and Excel Power Pivot.

DATESINPERIOD Syntax

DATESINPERIOD(<Dates>, <StartDate>, <NumberOfIntervals>, <Interval>)
  • Dates: A column containing date values.
  • StartDate: A scalar date value. This date is inclusive.
  • NumberOfIntervals: An integer representing the number of intervals to include.
  • Interval: The unit of time interval to use (DAY, MONTH, QUARTER, YEAR).

Example 1: Sales in the Last 3 Months

To calculate the total sales for the last 3 months from a given point in time:

Last3MonthsSales := 
CALCULATE(
    SUM(Sales[SalesAmount]), 
    DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -3, MONTH)
)

Explanation:

  • LASTDATE('Date'[Date]) gives the last available date in the date column.
  • DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -3, MONTH) returns all dates in the last 3 months from the last date.
  • CALCULATE(SUM(Sales[SalesAmount]), ...) then sums up the sales for these dates.

Example 2: YTD Sales (Year-To-Date Sales)

To calculate the Year-To-Date sales:

Power BI Tools Advertisement 
YTDSales := 
CALCULATE(
    SUM(Sales[SalesAmount]), 
    DATESINPERIOD('Date'[Date], FIRSTDATE('Date'[Date]), -1, YEAR)
)

Explanation:

  • FIRSTDATE('Date'[Date]) gives the first date of the current context.
  • DATESINPERIOD('Date'[Date], FIRSTDATE('Date'[Date]), -1, YEAR) returns all dates from the start of the context up to one year ago.
  • CALCULATE(SUM(Sales[SalesAmount]), ...) sums up the sales YTD.

Example 3: Moving Average of Sales Over Last 7 Days

To calculate the moving average of sales over the last 7 days:

MovingAverageSales :=
CALCULATE(
    AVERAGEX(
        DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -7, DAY),
        [TotalSales]
    )
)

Explanation:

  • LASTDATE('Date'[Date]) retrieves the last date in the current context.
  • DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -7, DAY) fetches the dates for the last 7 days.
  • AVERAGEX(...) calculates the average sales over the selected dates.
  • [TotalSales] is a pre-existing measure that sums the sales amount.

Example 4: Cumulative Total of Orders Over Last Year

To get a cumulative count of orders over the last year, you can use:

CumulativeOrdersLastYear :=
CALCULATE(
    COUNT(Sales[OrderID]), 
    DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -1, YEAR)
)

Explanation:

  • DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -1, YEAR) will provide the dates for the last one year period.
  • CALCULATE(COUNT(Sales[OrderID]), ...) counts the total orders within this period.

These examples demonstrate how the DATESINPERIOD function can be utilized for various practical scenarios in data analysis involving time-based calculations.

Advanced Techniques with DATESINPERIOD

In this section, we will implement advanced techniques using the DATESINPERIOD DAX function within Power BI. We will build on earlier lessons to perform more complex calculations and aggregations.

Advanced Rolling Sum Calculation

Scenario:

Calculate a 12-month rolling sum of total sales for a given period.

Implementation

Rolling12MonthSales = 
CALCULATE(
    SUM(Sales[TotalSales]),
    DATESINPERIOD(
        Sales[Date],
        LASTDATE(Sales[Date]),
        -12,
        MONTH
    )
)

Explanation:

  1. SUM(Sales[TotalSales]): Computes the sum of the ‘TotalSales’ column.
  2. DATESINPERIOD(Sales[Date], LASTDATE(Sales[Date]), -12, MONTH): Defines a period of the last 12 months ending at the latest date in the Sales[Date] column.
  3. CALCULATE: Recalculates the sum of sales within this period.

Advanced Year-To-Date (YTD) Calculation

Scenario:

Compute a YTD sales figure excluding certain dates (e.g., weekends or holidays).

Implementation

YTDExcludingWeekends = 
CALCULATE(
    SUM(Sales[TotalSales]),
    DATESINPERIOD(
        Sales[Date],
        STARTOFYEAR(Sales[Date]),
        -1,
        YEAR
    ),
    NOT(ISOWEEKNUM(Sales[Date]) = 6 OR ISOWEEKNUM(Sales[Date]) = 7)
)

Explanation:

  1. SUM(Sales[TotalSales]): Computes the sum of the ‘TotalSales’ column.
  2. DATESINPERIOD(Sales[Date], STARTOFYEAR(Sales[Date]), -1, YEAR): Defines a period from the start of the year.
  3. CALCULATE: Recalculates the sum of sales within this period while applying an additional filter to exclude weekends.

Dynamic Rolling Average Calculation

Scenario:

Calculate a dynamic 3-month rolling average.

Implementation

Rolling3MonthAvg = 
CALCULATE(
    AVERAGE(Sales[TotalSales]),
    DATESINPERIOD(
        Sales[Date],
        LASTDATE(Sales[Date]),
        -3,
        MONTH
    )
)

Explanation:

  1. AVERAGE(Sales[TotalSales]): Computes the average of ‘TotalSales’.
  2. DATESINPERIOD(Sales[Date], LASTDATE(Sales[Date]), -3, MONTH): Defines a period of the last 3 months.
  3. CALCULATE: Recalculates the average within this period.

Custom Fiscal Year Analysis

Scenario:

Analyze sales based on a custom fiscal year that starts in July.

Implementation

FiscalYearSales = 
CALCULATE(
    SUM(Sales[TotalSales]),
    DATESINPERIOD(
        Sales[Date],
        IF(
            MONTH(LASTDATE(Sales[Date])) >= 7,
            STARTOFYEAR(DATE(YEAR(LASTDATE(Sales[Date])), 7, 1)),
            STARTOFYEAR(DATE(YEAR(LASTDATE(Sales[Date])) - 1, 7, 1))
        ),
        1,
        YEAR
    )
)

Explanation:

  1. SUM(Sales[TotalSales]): Computes the sum of the ‘TotalSales’ column.
  2. IF(MONTH(LASTDATE(Sales[Date])) >= 7, STARTOFYEAR(DATE(YEAR(LASTDATE(Sales[Date])), 7, 1)), STARTOFYEAR(DATE(YEAR(LASTDATE(Sales[Date])) - 1, 7, 1))): Determines the start date of the fiscal year dynamically based on whether the current date is before or after July.
  3. DATESINPERIOD(...): Defines a period of one fiscal year.
  4. CALCULATE: Recalculates the sum of sales within this custom fiscal year period.

Each of these advanced implementations helps unlock deeper insights by leveraging the DATESINPERIOD function for various business scenarios.

Combining DATESINPERIOD with Other DAX Functions

Overview

This section will outline a practical implementation of combining the DATESINPERIOD function with other DAX functions.

Context

Given a table Sales with columns SalesDate, Amount, and ProductID, we aim to calculate the total sales amount for the last 30 days up to a selected date for each product.

Implementation

Step 1: Create a Measure for Total Sales

First, create a measure to calculate the total sales amount.

Data Mentor Advertisement 
TotalSales := SUM(Sales[Amount])
Step 2: Define the Date Range with DATESINPERIOD

Use DATESINPERIOD to define the rolling 30-day period up to the MAX date in the SalesDate column.

Last30DaysSales :=
CALCULATE(
    [TotalSales],
    DATESINPERIOD(Sales[SalesDate], MAX(Sales[SalesDate]), -30, DAY)
)
Step 3: Combine with Other Functions

Let’s use ALLEXCEPT to keep the context of ProductID and isolate it from SalesDate.

Last30DaysSalesByProduct :=
CALCULATE(
    [Last30DaysSales],
    ALLEXCEPT(Sales, Sales[ProductID])
)
Step 4: Using IF for Conditional Logic

For a more refined calculation, include conditional logic to handle cases where there might not be sales data available for a given period.

TotalSalesForLast30Days := 
IF(
    ISBLANK([Last30DaysSalesByProduct]),
    0,
    [Last30DaysSalesByProduct]
)
Resulting Implementation

Combining steps, the final calculation can be structured as follows:

TotalSales := SUM(Sales[Amount])

Last30DaysSales :=
CALCULATE(
    [TotalSales],
    DATESINPERIOD(Sales[SalesDate], MAX(Sales[SalesDate]), -30, DAY)
)

Last30DaysSalesByProduct :=
CALCULATE(
    [Last30DaysSales],
    ALLEXCEPT(Sales, Sales[ProductID])
)

TotalSalesForLast30Days := 
IF(
    ISBLANK([Last30DaysSalesByProduct]),
    0,
    [Last30DaysSalesByProduct]
)

These DAX measures together will allow you to calculate and analyze the total sales amount for each product over the last 30 days effectively.

Hands-On Projects and Assignments: DATESINPERIOD

Overview

This section contains practical exercises to master the DAX DATESINPERIOD function effectiveness. These tasks will solidify your understanding by applying the function in real scenarios.

Project 1: Calculate Cumulative Sales Over a Period

Objective

Create a measure to calculate cumulative sales over the last 90 days from any given date.

Steps

  1. Initialize your data model: Ensure you have a Sales table and a Date table linked by a date key.
  2. Define the Measure:
    CumulativeSalesLast90Days :=
    CALCULATE(
        SUM(Sales[SalesAmount]),
        DATESINPERIOD(Date[Date], MAX(Date[Date]), -90, DAY)
    )
    • SUM(Sales[SalesAmount]): Computes the total sales amount.
    • DATESINPERIOD(Date[Date], MAX(Date[Date]), -90, DAY): Defines the period to be the last 90 days from the current context date.

Validation

  • Test your measure by creating a table visual in your BI tool, and add Date and CumulativeSalesLast90Days to see the rolling sales over the last 90 days.

Project 2: Year-to-Date (YTD) Calculation

Objective

Build a measure that calculates the Year-to-Date (YTD) sales from the start of the year to the max date in the context.

Steps

  1. Define the Measure:
    YTDSales :=
    CALCULATE(
        SUM(Sales[SalesAmount]),
        DATESINPERIOD(Date[Date], STARTOFYEAR(Date[Date]), -1, MONTH)
    )
    • DATESINPERIOD(Date[Date], STARTOFYEAR(Date[Date]), -1, MONTH): Selects dates from the start of the current year up to the maximum date in the context.

Validation

  • Create a visual with Date and YTDSales to verify your results. Ensure the values accumulate correctly through the year.

Assignment 1: Moving Average Calculation

Objective

Compute a 30-day moving average of sales.

Task

  • Develop the measure:

    MovingAverage30Days :=
    AVERAGEX(
        DATESINPERIOD(Date[Date], MAX(Date[Date]), -30, DAY),
        CALCULATE(SUM(Sales[SalesAmount]))
    )
    • AVERAGEX iterates over the 30-day period, calculating the average of the sales amount.

  • Visualize and Validate:

    • Use a line chart to plot Date against MovingAverage30Days. Confirm that the line smoothens the short-term fluctuations over the 30 days.

Assignment 2: Custom Time Period Calculation

Objective

Calculate total sales for any custom period specified by user input parameters (start date and end date).

Task

  • Input Parameters: Define two parameters StartDate and EndDate.


  • Measure Definition:

    CustomPeriodSales :=
    CALCULATE(
        SUM(Sales[SalesAmount]),
        DATESBETWEEN(Date[Date], [StartDate], [EndDate])
    )
    • DATESBETWEEN(Date[Date], [StartDate], [EndDate]): Filters the dates between the provided start and end dates.

  • Validate:

    • Create a slicer or input form for StartDate and EndDate and verify the measure displays corresponding sales correctly.

Conclusion

These hands-on projects and assignments are aimed at enhancing your practical skills with the DATESINPERIOD function and related DAX functionalities. Ensure you follow each step precisely and validate the results within your BI tool to ensure accuracy.

Related Posts