Methods of Comparing Current and Prior Year Facts

[RightMeow]

I have a fact table called [Sales], and in it, I have daily sales for calendar years 2011, 2012, and 2013 described with two columns: [Date] and [Revenue].

I also have set up a date table called [DimDate] where there is a contiguous set of dates that cover the years in question on a daily basis in a column called [DateKey]. Actually, my [DimDate] table goes all the way from 1975 through 2020. I have established a relationship between [Sales] and [DimDate] tables through the DimDate[DateKey] and Sales[Date] columns.

In any case, I have the time honored problem of comparing one year's sales on a daily basis and on a monthly basis. I have two methods that seem to work, and I would like to get some pros and cons of each approach (since I am not able to see what advantages one approach has over the other).

To begin, I define a measure on the [Sales] table called [Sum of Revenue]:=SUM([Revenue]).

Here are my two approaches to defining a measure called [Sales Amount Last Year] on the [Sales] table.

Approach 1: Define [Sales Amount Last Year]:=CALCULATE([Sum of Revenue], SAMEPERIODLASTYEAR(DimDate[DateKey])).

Approach 2: Define [Sales Amount Last Year]:=CALCULATE([Sum of Revenue],DATEADD(DimDate[DateKey], -1,YEAR)).

Approach 1 uses the SAMEPERIODLASTYEAR function, and Approach 2 uses the DATEADD function.

Has anyone encountered a situation where they preferred to use one over the other, and can you please share why you chose the one that you did?

 

[scottsen]

My gut: You will notice zero difference in performance nor functionality.

 

[jersey]

In this case , how would you do YTD?

 

[RightMeow]

jersey,

The way I handled it is as follows:



For the current year’s year-to-date sales, I define the following measure (using the TOTALYTD function):



[Year to Date Sales]:=TOTALYTD([Sum of Revenue], DimDate[DateKey])


To then do the same for the prior year’s year-to-date sales, I define the following measure (using the just created [Year to Date Sales] measure:



[Year to Date Sales Last Year]:=CALCULATE([Year to Date Sales], SAMEPERIODLASTYEAR(DimDate[DateKey]))



I decided to stick with the SAMEPERIODLASTYEAR function; however, I think the following should also work, if we use the DATEADD function.



[Year to Date Sales Last Year]:=CALCULATE(Year to Date Sales], DATEADD(DimDate[DateKey], -1, YEAR)).



For my model, I just compared both of these ways of constructing the [Year to Date Sales Last Year] measures, and they yield the same results (so far).