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] )
)
)
