Calendar-based time intelligence

In the Ultra Transcenders books

PL-300

Each book explains Calendar-based time intelligence in context, with comparison tables and the common traps.

Terms in this definition

• DAX

  Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.

• Time intelligence

  Covers DAX functions (TOTALYTD, DATEADD, SAMEPERIODLASTYEAR and others) that shift or accumulate filtered dates. Classic versions require a contiguous, marked date table; calendar-based versions, still in preview, rely on defined calendars.

• Set

  Secret permission in Key Vault for writing secrets; some older material refers to it as Create.

• Event

  Table in Log Analytics where entries from Windows event logs are kept.

• CALENDAR

  Generates a date table as a calculated table: given a start and end date, this DAX function returns a single column, called Date, listing every day in between, including both ends.

• Table options

  Set only at creation with OPTIONS, these key-value storage settings can't be altered or dropped afterwards; on Delta tables they also show up among the table properties.

• ALL

  A DAX function that ignores any filters and gives back every row of a table or every value of the named columns. Used within CALCULATE, it works as a modifier that clears filters, although REMOVEFILTERS states that intent more clearly where it is available.

Related terms

• CLOSINGBALANCEWEEK

  Calculates an expression's value on the final day of the week in context. Like every week-level DAX time function, it requires calendar-based time intelligence.

• DATESWTD

  Gives the dates from the week's first day up to the latest date in context. Calendar-based time intelligence is required to use it.

• ENDOFYEAR

  Gives back the last day of the year in context. It can work from a date column, optionally with a custom year-end date, or from a calendar when calendar-based time intelligence is in use.

• NEXTDAY

  Its argument is normally a date column, or a calendar when you use calendar-based time intelligence; this DAX function then gives back the day that follows the latest date currently being evaluated.

• OPENINGBALANCEWEEK

  Gives the value of an expression as last week closed, which is this week's opening figure. It must be handed a calendar, so it only works when calendar-based time intelligence is in use.

• TOTALMTD

  Works out the running month-to-date figure for a DAX expression, based on dates in a column or, if calendar-based time intelligence is in use, on a calendar.

• TOTALQTD

  Works out the running quarter-to-date figure for a DAX expression, based on dates in a column or, if calendar-based time intelligence is in use, on a calendar.

• TOTALWTD

  Calculates week-to-date results for an expression. Week functions like this one need calendar-based time intelligence.