7. dax time intelligence

time intelligence means modifying the filter context for date filters.

If you have irregular time periods e.g.

• financial months that begin mid-way through the calendar month
• or you need to work with weeks or time periods (hours, minutes)

the DAX time intelligence functions won’t be helpful.

• soln: need to use the CALCULATE function and pass in hand-crafted date or time filters.

date table requirement

requires date table for time intelligence

• 1 col of type date
• date col have unique val
• date col have 0 blanks
• date col have no missing date
• date col span full year
• date table marked as date table

Summarizations over time

• datesytd: return 1 col table
• totalytd: eval YTD in current filter ctx
• datesbetween: return 1 col date between
• datesinperiod: return 1 col date with start + n interval

Comparisons over time

shifting time periods

• dateadd
• parallelperiod
• sameperiodlastyear
• nextday, nextmonth, nextquarter, nextyear, previousday, previousmonth, previousquarter, previousyear

use togather with calculate to change eval filter context

Revenue PY =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    RevenuePriorYear

e.g.

Revenue YoY % =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    DIVIDE(
        [Revenue] - RevenuePriorYear,
        RevenuePriorYear
    )

Additional time intelligence calculations

single date

firstdate, lastdate

Calculate new occurrences