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