THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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 =
    SAMEPERIODLASTYEAR ( CurrentRange )
RETURN
    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: , , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

thomas said:

Marco,

That is really cool.

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

Thomas

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 https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ for some example

February 15, 2017 12:00 PM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Privacy Statement