THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Year-over-year comparison using the same number of days in #dax

When you use the time intelligence functions in DAX, it is relatively easy to filter the same dates selection in the previous year by using the SAMEPERIODLASTYEAR or DATEADD functions. However, if you follow the best practices, it is likely that you have a full date table for the current year, which includes many days in the future. If you are in the middle of March 2017, you have sales data until March 15, 2017, so you might want to compare such a month with the same number of days in 2016. And the same when you compare the Q1, or the entire year.

A common solution is to translate this request in a month-to-date (MTD) or quarter-to-date (QTD) comparison, but depending on how you implement this, you might not obtain a reliable result. For example, you might assume that the current date on your PC is the boundary of the dates you want to consider, but you probably have a few hours if not days of latency in the data in your database, so you should constantly fix the offset between the current day and the last day available in your data.

Thus, why not simply relying on the data you have to make an automatic decision? This is the purpose of the technique described in the article Compare equivalent periods in DAX that I wrote on SQLBI, where I show several approaches optimized for Power BI, Excel, and SSAS Tabular, which are different depending on the version you use.

Personally, the version I prefer is the one with the variables in DAX:

[PY Last Day Selection] :=
VAR LastDaySelection =
    LASTNONBLANK ( 'Date'[Date], [Sales Amount] )
VAR CurrentRange =
    DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ), LastDaySelection )
VAR PreviousRange =
    IF (
        LastDaySelection > MIN ( 'Date'[Date] ),
        CALCULATE ( [Sales Amount], PreviousRange )
It’s longer, but much more readable. More details and examples you can download for Excel and Power BI are available in the article.
Published Tuesday, January 24, 2017 1:50 PM by Marco Russo (SQLBI)
Filed under: , , , ,



thomas said:


That is really cool.

Do you have something like this for MDX or a Cube/Multidimensional?


January 24, 2017 9:07 AM

Pedzilla said:

Hi there

does this work for 4-4-5 calendar?

Thanks very much

February 7, 2017 10:22 AM

Marco Russo (SQLBI) said:

@Thomas: no, sorry

@Pedzilla: you have to rely on DAX implementation of 4-4-5 instead of relying on SAMEPERIODLASTYEAR - see for some example

February 15, 2017 12:00 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement