Dax Function: DATESINPERIOD
Category: Time Intelligence Functions
The DATESINPERIOD function in Power BI is a Time Intelligence function that returns a table containing a set of date values from a specified period relative to a reference date. This function is ideal for dynamic time-based calculations, such as trailing or forward periods.
Purpose of the Function
The primary purpose of DATESINPERIOD is to dynamically create a date range based on a reference date and a specified interval, which can be in days, months, quarters, or years. It allows flexibility in creating relative date filters for measures and reports.
Type of Calculations
- Provides a rolling or moving date window.
- Filters data for dynamic periods, such as the last 30 days, the next quarter, or the trailing year.
- Enables time-based aggregations like sums, averages, or counts over the generated date range.
Practical Use Cases
- Trailing Metrics: Calculate sales over the last 12 months from a given date.
- Future Projections: Generate forecasts for the next quarter or year.
- Comparative Analysis: Compare metrics across dynamic time windows, such as this quarter vs. the same period last year.
- Custom Period Filters: Use it to filter data for non-standard reporting intervals, such as fiscal periods or marketing campaign durations.
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)
| Parameter | Type | Description |
|---|---|---|
dates | Column | A column containing date values, typically from a date table. |
start_date | Scalar | A single reference date that defines the starting point of the period. |
number_of_intervals | Scalar | A signed integer that specifies the size and direction of the period. Positive values indicate a future period; negative values indicate a past period. |
interval | String | A time unit defining the interval: DAY, MONTH, QUARTER, or YEAR. |
How Does DATESINPERIOD Dax Function Works?
- Reference Date: The function uses the
start_dateas the anchor point. - Interval Calculation: Based on
number_of_intervalsandinterval, it calculates the start and end boundaries of the date range. - Date Filtering: It generates a table of dates that fall within the computed range, which can be used as a filter context in other DAX calculations.
What Does It Return?
The function returns a table of date values filtered to include only the dates within the specified interval and direction relative to the start_date.
When Should We Use It?
- Use
DATESINPERIODto define dynamic time windows for rolling calculations. - Helpful for reports with variable time ranges (e.g., “Last 7 days” or “Next 3 months”).
- Ideal for building trend analyses or moving averages.
Examples
Basic Usage
Sales Last 30 Days = CALCULATE(
SUM(Sales[Amount]),
DATESINPERIOD(Dates[Date], TODAY(), -30, DAY)
)
This calculates the total sales for the last 30 days from today.
Column Usage:
Active Customers Last Quarter = CALCULATE(
COUNT(Customers[CustomerID]),
DATESINPERIOD(Dates[Date], TODAY(), -1, QUARTER)
)
Counts active customers over the last quarter.
Advanced Usage
Rolling 12-Month Revenue = CALCULATE(
SUM(Sales[Revenue]),
DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -12, MONTH)
)
This calculates the revenue for the trailing 12 months from the most recent date in the context.
Tips and Tricks
- Use a Continuous Date Table: Ensure your
datescolumn comes from a complete and continuous date table. - Leverage Dynamic Dates: Use
TODAY()orNOW()for dynamic periods in live dashboards. - Signed Intervals: Positive intervals look forward, while negative intervals look backward from the reference date.
- Combine with CALCULATE: This function works best when used inside
CALCULATEto apply a filter to specific measures.
Pitfalls:
- Avoid using incomplete date tables, as it may result in missing or inaccurate periods.
- The function only works with valid date columns.
Performance Impact of DATESINPERIOD DAX Function:
- Ensure your date table is optimized for performance with appropriate indexing.
- For large datasets, limit the date range to avoid excessive memory consumption.
Related Functions You Might Need
DATESBETWEEN: Returns dates within a fixed range defined by explicit start and end dates.FIRSTDATEandLASTDATE: Identify the first and last dates in a filtered context.DATESYTD: Filters dates from the start of the year to the current date.CALCULATE: Applies filters, including those generated byDATESINPERIOD.
Want to Learn More?
For more information, check out the official Microsoft documentation for DATESINPERIOD. You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
It generates a table of dates based on a reference date and a specified interval, such as days, months, quarters, or years.
DATESINPERIOD dynamically calculates a range relative to a reference date, while DATESBETWEEN uses fixed start and end dates.
Yes, by using a positive value for number_of_intervals, you can generate forward-looking periods.
The function may return incorrect or missing results if the date column has gaps.
Yes, the interval includes the start_date in the returned table of dates.
Established in 2020, Lets Viz Technologies provides a full range of high-quality data analysis and data visualization services. We are also an authorized Zoho Partner.
Sitelinks
