Topic: Time Intelligence

        10 posts
blog post –

International year_end_date for YTD functions in DAX

If you used the DATESYTD and TOTALYTD functions in DAX, you might have noticed that the optional parameter year_end_date is a string defining the last day of the year. The default is December 31, so this parameter is used only…  Read more

article –

Reference Date Table in DAX and Power BI

This article describes a reference Date table in DAX using a Power BI template. The same technique can be used in Analysis Services models. Download the latest version of the template in the Dax Date Template page.  Read more

article –

Compare equivalent periods in DAX

If you do not have a full month with data, comparisons such as year-over-year (YOY) might require particular filters in order to do a like for like comparison in DAX. This article describes how to write DAX expressions comparing equivalent…  Read more

article –

Time Intelligence in Power BI Desktop

In Power BI Desktop (as of February 2016) you have to use DAX to apply calculations over dates (such as year-to-date, year-over-year, and others), but you do not have the Mark as Date Table feature. This article describes which scenarios…  Read more

article –

Alternative use of FIRSTNONBLANK and LASTNONBLANK

You might have used FIRSTNONBLANK and LASTNONBLANK in semi-additive measures, but you might not be aware that their use is not limited to time intelligence functions. This article shows alternative scenarios where these functions are useful.  Read more

article –

Custom Year-Over-Year Calculation in DAX

This article describes how to implement a custom year-over-year calculation in DAX based on arbitrary associations between different periods. As an example, we describe the comparison of the 53rd week in a ISO Calendar.  Read more

article –

Rolling 12 Months Average in DAX

Computing the rolling 12-months average in DAX looks simple, but it hides some level of complexity. This article explains how to write the best possible formula and how to avoid common pitfalls using time intelligence functions.  Read more

article –

Semi-Additive Measures in DAX

Values such as inventory and balance account, usually calculated from a snapshot table, require the use of semi-additive measures. In Multidimensional you have specific aggregation types, like LastChild and LastNonEmpty. In PowerPivot and Tabular you use DAX, which is flexible…  Read more

article –

Week-Based Time Intelligence in DAX

The DAX language provides several Time Intelligence functions that simplify writing calculations such as year-to-date (YTD), year-over-year (YOY) and so on. However, if you have a special calendar structure such as a 4-4-5 weeks’ calendar, you need to write your…  Read more