Dating tools by patern

03 Jan

The Date table must contain all the attributes used in the calculation, in a numeric format.

For example, the fiscal calendar shown in Figure 3 has strings for visible columns (Year, Month, Quarter, and Week Day), along with corresponding numeric values in other columns (Year Number, Month Number, Quarter Number, and Week Day Number).

The pattern is very flexible and moves the business logic of the time-related calculations from the DAX predefined functions to the content of the Date table.

The following is a list of some interesting use cases.

However, when you implement custom time-related calculations, it is always a good practice to mark a table as a date table, even if you do not use the DAX time intelligence functions.

You can use the Time Intelligence pattern whenever using the standard DAX time intelligence functions is not an option (for example, if you need a custom calendar).

The only difference in each formula is the condition that checks whether the date belongs to the considered aggregation or not.

The general formula will be: When you define an aggregation, usually you extend the period considered to include all the days elapsed since a particular day in the past.

This approach is common in the retail and manufacturing industries, where the 4-4-5 calendar, 5-4-4 calendar, and 4-5-4 calendar are used.

The DAX time patterns are used to implement time-related calculations without relying on DAX time intelligence functions.

This is useful whenever you have custom calendars, such as an ISO 8601 week calendar, or when you are using an Analysis Services Tabular model in Direct Query mode.

You will hide the numeric values from client tools in the data model but use them to implement time intelligence calculations in DAX and to sort the string columns.

Any aggregation over time filters the Date table to include all the dates in the period considered.