Time intelligence functions

Intermediate DAX in Power BI

Maarten Van den Broeck

Content Developer at DataCamp

Time intelligence functions

Manipulate and compare data using time periods

line chart of sales data over time

Many time intelligence functions exist

$$ $$ $$

Compare current period with previous period
Estimate monthly/quarterly/yearly goals

Time intelligence functions returning a date

NEXTDAY(<dates>)
- Returns the next day

dates	NEXTDAY
2009-07-07	2009-07-08
2009-07-08	2009-07-09
2009-07-09	2009-07-10

Time intelligence functions returning a date

NEXTDAY(<dates>)
- Returns the next day
SAMEPERIODLASTYEAR(<dates>)
- Returns the last year $$ $$ $$ $$
DATESBETWEEN(<dates>, <start_date>, <end_date>)
- Returns dates between start and end date

dates	NEXTDAY	LASTYEAR
2009-07-07	2009-07-08	2008-07-07
2009-07-08	2009-07-09	2008-07-08
2009-07-09	2009-07-10	2008-07-09

Time intelligence functions returning a date

NEXTDAY(<dates>)
- Returns the next day
SAMEPERIODLASTYEAR(<dates>)
- Returns the last year $$ $$ $$ $$
DATESBETWEEN(<dates>, <start_date>, <end_date>)
- Returns dates between start and end date

dates	NEXTDAY	LASTYEAR
2009-07-07	2009-07-08	2008-07-07
2009-07-08	2009-07-09	2008-07-08
2009-07-09	2009-07-10	2008-07-09

dates	DATESBETWEEN
2009-07-07
2009-07-08	2009-07-08
2009-07-09	2009-07-09
2009-07-10

Time intelligence functions returning a date

Mid Season Sales =
CALCULATE(
    SUM(Fact_Table[Sales]), 
    DATESBETWEEN(Dim_Date[Date Key], 
                 DATE(2014, 10, 04),
                 DATE(2014, 10, 26)
                 )
)

Time intelligence functions returning a date

TOTALYTD(<expression>, <dates> [,<filter>])
TOTALQTD(<expression>, <dates> [,<filter>])
TOTALMTD(<expression>, <dates> [,<filter>])

Returns the year, quarter, or month to date value of the expression.

Sum_YTD = 
TOTALYTD(
    SUM(Fact_Table[Value]),
    Dim_Date[Date Key]
)

Time intelligence functions returning a date

TOTALYTD(<expression>, <dates> [,<filter>])
TOTALQTD(<expression>, <dates> [,<filter>])
TOTALMTD(<expression>, <dates> [,<filter>])

Returns the year, quarter, or month to date value of the expression.

Sum_YTD = 
TOTALYTD(
    SUM(Fact_Table[Orders]),
    Dim_Date[Date Key]
)

Year	Month	Value	Sum_YTD
2021	Jan	6,532	6,532
2021	Feb	4,263	10,795
2021	Mar	1,256	12,051
Total		12,051	12,051

Best practices for time intelligence functions

Use a separate date dimension table

Simple relationship between fact table and dimension date table

A date column in the fact table could contain missing dates!

Let's practice!

Intermediate DAX in Power BI