DEV Community

Cover image for Time Intelligence Functions (DAX)
John Kyalo
John Kyalo

Posted on Edited on

Time Intelligence Functions (DAX)

Long time no Data chat around here.

Let's talk about 3 crucial time intelligence functions in Power BI namely DATEADD, DATESINPERIOD and DATESBETWEEN.

Basically, they all shift or define a date range, especially for creating time-based comparisons like:
same period last year
Trailing 12 months
Custom range
They differ in how they define the range and what you pass in the parameters.

Let's dive in:

DATEADD
Shifts the dates I have forward or backward in time by a specified number of Intervals.
I think of it as shifting the dates I have by a fixed number of units.

It is best for YoY, QoQ and MoM calculations.

DATESINPERIOD
Returns a continuous date range (before or after a reference date), going back or forward by a fixed number of Intervals.
I think of it as, "Give me a window of dates around a specific date"

It is best for trailing periods e.g. (rolling 12 months)

DATESBETWEEN
Returns all dates between a start and an end date.
I think of it as manually specifying the start and end of the range.

It is best for exact range filtering:
YTD, QTD, MTD, Sales Jan - March

In summary, DATEADD is a shift while DATESINPERIOD & DATESBETWEEN are range selectors.

A key thing to note:
DATEADD needs a complete date table as it is not gap-tolerant. Such issues are the reasons we also choose to have a dedicated Dim Calendar table.

That's it for today and see you on the next one.

Anything & Everything Data

Top comments (0)