DAX has lots of useful Time Intelligence functions and in this recipe I will pay some attention to these that filter aggregations according to beginning or the end of a period. My friends from ASEE would say: “From the cradle to the grave is the most beautiful is go-go phase”. However, in this recipe I would hold to quantitative results, and DAX helps us to determine them in the easy way (that’s my field of expertise)!
Reports often require that in addition to the usual review of the sales we create running total, i.e. to track cumulative growth of sales from beginning of the month, quarter or year. For this purpose we use the Time Intelligence functions DATESMTD, DATESQTD and DATESYTD by which we can track cumulative sales growth since the beginning of the given period. If you want to find out more please read the recipe that follows…
Time Intelligence functions are often used for reviewing implementation of the goals at the same time last year. In what follows we will mention a few of DAX functions that cover this area. First, there is a function SAMEPERIODLASTYEAR which should monitor business results in the same period last year. And then there are functions DATEADD and PARALLELPERIOD by which we can monitor the achievement of objectives in the previous or next period (month, quarter, or year) in relation to the current data.
If we want to carry out calculations in previous or the next period in relation to the current date, should use “PREVIOUS” and “NEXT” Time Intelligence function. They allow aggregation of data, usually the column that contains the quantity or amount of sales, compared to the previous year, quarter, month or day, as well as in relation to the next year, quarter, month or day. In the example below you will see how to utilize this class of DAX functions.
DAX has a variety of functions for working with date and time which do not differ much from similar traditional Excel functions. Time Intelligence functions, on the other hand, are a special group of DAX functions that enable calculations in accordance with time periods: years, quarters, months and days. These periods are used as special filters that allow achieving of the results that we quite hardly get by using traditional calculation methods.