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

Customize period comparison in DAX #powerpivot #dax #powerbi

I have seen many different types of custom calendars implemented in Power Pivot, Tabular, and DAX. I also wrote several articles (week based calendars and time patterns). However, there is a scenario that is hard to manage with the classical approaches, which I discussed in a new article.

Suppose you want to implement custom comparisons. For example, if you select a range of dates including the Thanksgiving day, how do you compare that period with the previous year? For example, Thanksgiving was in November 27 in 2008 and in November 22 in 2007. Two different weeks also from the calendar point of view. Similar problems when you want to compare Easter days. Not to mention any particular requirements when you want to define an arbitrary correspondence between days of one year with the previous year. You can see an example of this in the ISO YOY Date column in the following screenshot.


With DAX it’s relatively easy implementing this approach by using the Calendar table. You simply store for each day the corresponding day in the previous year, and write a DAX formula that will use this correspondence to apply the filter for the comparison, regardless of the selected range of days (it will work also when you select weeks, months, or quarters). You can fine more details and download an example in the Custom Year-Over-Year Calculation in DAX article on SQLBI web site.

Published Thursday, February 5, 2015 10:32 AM by Marco Russo (SQLBI)
Filed under: , , ,



Roberto said:

Hi Marco,

Thank you for your blog posts!

Speaking about comparisons, I'm looking for a way to compare the net sales of a fashion company over 2 seasons. Usually they compare the sales of the current season, let's say Fall Winter 2014 (FW14), with the sales of the previous corresponding season = FW13.

I have a fact table that records every single sales line: item code, category, gender, season, quantity, price, etc.

do you think there is a way to have a measure that calculates the "previous season sales" automatically for any dimension I would use in the report?

Thank you,


February 24, 2015 4:43 PM

Marco Russo (SQLBI) said:

Why you don't add two columns: "Season" and "Previous Season" for each row... it should be enough (I would populate the two columns using a string with both season and year, such as "Winter 2014")

February 24, 2015 5:56 PM

Roberto said:

Thank you for your reply but I don't get it.

If I add a column "previous season" to the facts table, how do I then calculate the sales for that previous season considering that in that record I have the sales amount of an item of the current season?

Thank you,


February 24, 2015 6:06 PM

Marco Russo (SQLBI) said:

Not in the fact table - in the date table.

February 24, 2015 6:11 PM

Roberto said:

There is no date table as seasons are not strictly related to a time period / dates between.

Season is a dimension of the items and the goal is to compare the sales amount of a season vs the sales amount of the previous season.

Thank you,


February 24, 2015 6:16 PM

Marco Russo (SQLBI) said:

So it seems a modeling problem. However, somewhere you should have the information about this (it could be a hierarchy of attributes, with a detail for seasons of different articles). But if dates are different for each article, it's no longer a date-related issue, it's just a regular data modeling issue....

February 24, 2015 6:31 PM

Bill said:

Year over Year Variance Drilldown - Excel Pivot Table or PowerPivot

Upon researching via the internet, I came across this site...

My original objective was to perform a monthly import of a text file containing two years of data into MS Excel 2013 and create a Year over Year, by month analysis using vba or some other process and determine the following;

Step 1: Top 5 months with the greatest variances - Year over Year

Step 2: Then, the Top 5 providers for the 5 months in Step 1

Step 3: Then, the Top 5 customers (by PaidAmt) for the 5 providers in Step 2

The text file contains 2013 and 2014 data and consists of the following columns;

Date,  Provider, CustomerName, PaidAmt

Currently, I manually create the three pivot tables and perform the analysis which is somewhat time consuming.

Pivot table #1 - I have the PaidAmt by Year and Month.  For example,

Row: Month

Column: Year

Values: Sum of PaidAmt, Difference, %-age Increase(Decrease)

Pivot table #2 - I have the PaidAmt by Month and providers.  And, the third pivot table is the PaidAmt by Provider and by CustomerName.

Note, I cannot rank on the calculated column, "%-age Increase(Decrease)", within a pivot table - whether creating the pivot table manually or with VBA.

Any suggestions on how best to "automate" this process using time intelligence functions within PowerPivot?

Thanks in advance for any insight.

October 11, 2015 1:40 AM

Marco Russo (SQLBI) said:


you should take a look at the option of creating custom dynamic Set in a pivot table using the MDX language. Not easy, but it would perfectly fit your requirements.

October 17, 2015 9:41 AM

Bill said:

Thanks for the insight.

Just to ensure that I comprehend...

Are you implying that the creation of custom dynamic Set in a pivot table using MDX will allow one to resolve this issue more so than the use of;

PowerPivot with the use of time intelligence functions

or Using SSIS to import the text file into Sql Server and using T-SQL?

Currently researching the creation of custom dynamic Set using MDX...

Any preferred resources and/or tools to facilitate the learning?

October 23, 2015 2:45 PM

Marco Russo (SQLBI) said:

Dynamic sets require knowledge of MDX, which is a hard language. I don't think DAX can satisfy all of your requirements without some accepting some limitation in flexibility.

October 23, 2015 6:09 PM

Marco Russo (SQLBI) said:

Dynamic sets require knowledge of MDX, which is a hard language. I don't think DAX can satisfy all of your requirements without some accepting some limitation in flexibility.

October 23, 2015 6:09 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