DAX has many time intelligence functions that are often redundant, offering different shorter syntaxes for longer more generic functions. However, sometimes the shorter syntax could be dangerous, as I explain in this blog post where I suggest using CALCULATE and…  Read more

Every Microsoft Power BI model has dates and the need for calculation over dates to aggregate and compare data, like year-to-date, same period last year, moving average, and so on. Quick measures and DAX functions can help, but how do…  Watch now

If you have a fiscal year starting in March, you can have a problem using the time intelligence functions in DAX. For example, by using February 28, the fiscal year starts on February 29th in leap years. By using February…  Read more

Time Intelligence is probably the most interesting feature of any analytical solution. Computing Year-To-Date, Month-To-Date, Same-Period-Last-Year, is quite easy in DAX but, as soon as the customer requests for time intelligence comparing data from different tables, or to manage non-standard…  Watch now

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

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

This article introduces a technique to filter and productively compare two time periods with Power BI.  Read more

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

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

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