Time Intelligence in DAX

Akash Jain Oct 28 2020 · 1 min read
Share this

Date Table

o Time intelligence needs a date table

• Built in DAX Or in a SQL Table

o Date table properties

• All dates should be present

• From 1° of January, to 31° of December

• No holes

• Otherwise time intelligence will not work

CALENDAR 2015

Returns a table with a single column named “Date” containing a contiguous set of dates in

the given range, inclusive.

CALENDAR (

DATE ( 2005, 1, 1 ),

DATE ( 2015, 12, 31 )

)

CALENDAR (

MIN ( Sales[Order Date] ),

MAX ( Sales[Order Date] )

)

CALENDARAUTO

Automatically creates a calendar table based on the database content. Optionally you can

specify the last month (useful for fiscal years) --

-- The parameter is the last month

-- of the fiscal year

--

= CALENDARAUTO (

6

) Beware: CALENDARAUTO uses

all the dates in your model,

excluding only calculated

columns and tables

2015

Set Sorting Options

o Month names do not sort alphabetically

• April is not the first month of the year

o Use Sort By Column

o Set all sorting options in the proper way

o Beware of sorting granularity

• 1:1 between names and sort keys

What is Time Intelligence?

o Many different topics in one name

• Year To Date

• Quarter To Date

• Running Total

• Same period previous year

• Working days computation

• Fiscal Year

o In short: anything related with time Handled in an intelligent way

Aggregations Over Time

o Many useful aggregations

• YTD: Year To Date

• QTD: Quarter To Date

• MTD: Month To Date

o They all need a Calendar Table And some understanding of CALCULATE

Year To Date (Time Intelligence)

DATESYTD makes filtering much easier

SalesAmountYTD :=

CALCULATE (

SUM ( Sales[SalesAmount] ),

DATESYTD ( 'Date'[Date] )

)

Handling Fiscal Year

The last, optional, parameter is the end of the fiscal year

Default: 12-31 (or 31/12 - locale dependent)

SalesAmountYTD :=

TOTALYTD (

SUM ( Sales[SalesAmount] ),

'Date'[Date],

"06-30"

)

SalesAmountYTD :=

CALCULATE (

SUM ( Sales[SalesAmount] ),

DATESYTD ( 'Date'[Date], "06-30" )

)

Same Period Last Year

Same period in previous year. CALCULATE is needed

Specialized version of DATEADD

Sales_SPLY :=

CALCULATE (

SUM ( Sales[SalesAmount] ),

SAMEPERIODLASTYEAR ( 'Date'[Date] )

)

Running Total

Running total requires an explicit filter

SalesAmountRT :=

CALCULATE (

SUM ( Sales[SalesAmount] ),

FILTER (

ALL ( 'Date' ),

'Date'[Date] <= MAX ( 'Date'[Date] )

)

)

Thank you!

Comments
Read next