SSAS DAX time intelligence previous year and calculation gives wrong value

Question

We want to show current periods sales versus previous period sales. To show the previous period we make use of a date dimenion table and the following calculation:

CALCULATE(SalesValueGross; DATEADD(Date[Date]; -1; YEAR))

Unfortunately somehow it shows minor (decimal) differences when comparing years. The difference get's bigger when we slice to months.

Another issue we have is that this calculation does not seem to work when comparing (for example) week 1 - 2015 with week 1 - 2014.

Any help is greatly appreciated!

mmarie mmarie · Accepted Answer · 2015-04-02T15:18:05

When I want to get prior calendar year sales, I use the a formula such as the following:

Cal Prior Yr Sales:=if(HASONEVALUE('Sale Date'[Calendar Year]),
Calculate([Total Sales], 
PREVIOUSYEAR('Sale Date'[Date])),BLANK())

The HASONEVALUE just ensures that there is only one year selected so it will know the correct previous year to retrieve.

You can make a series of calculations that will let you use one calc that determines what level of the date hierarchy you are in (assuming you have the fields available in your date table). Here is something I've used in the past, with a fiscal calendar that was different from the normal calendar.

First, the base calculations:

Sales Same Week Prior Year:=
CALCULATE([Total Sales],Filter(All('Sale Date'), 
'Sale Date'[Week Key] = max('Sale Date'[Same Week Last Year])))

Sales Same Month Prior Year:=CALCULATE([Total Sales], Filter(All('Sale Date'),
'Sale Date'[Month Seq] = max('Sale Date'[Month Seq])-12))

Sales Same Quarter Prior Year:=CALCULATE([Total Sales], Filter(All('Sale Date'), 
'Sale Date'[Quarter Seq] = max('Sale Date'[Quarter Seq])-4))

Sales Prior Year:=CALCULATE([Total Sales], Filter(All('Sale Date'), 
'Sale Date'[Fiscal Year] = max('Sale Date'[Fiscal Year])-1))

You can hide all of those calculations and then create one last calculation and leave it visible:

Sales Same Period Last Year:=
if(HASONEVALUE('Sale Date'[Week Key]), [Sales Same Week Prior Year], 
if(HASONEVALUE('Sale Date'[Month Key]),[Sales Same Month Prior Year],
if(HASONEVALUE('Sale Date'[Quarter Key]),[Sales Same Quarter Prior Year], 
if(HASONEVALUE('Sale Date'[Fiscal Year]), [Sales Prior Year], BLANK()))))

You may need to add a couple of calculated fields to your date table to make it work. I have fields for: [Same Week Last Year], [Month Seq], [Quarter Seq]. Same week last year is an integer field that is yyyyww. Month Seq and Quarter Seq are just autoincrementing integers in chronological order that do not repeat. My formula for same week last year is

=if('Sale Date'[Week Nbr] = 53, (('Sale Date'[Fiscal Year]-1) * 100) + ([Week Nbr]-1),
(('Sale Date'[Fiscal Year]-1) * 100) + ([Week Nbr]))

I did the sequence numbers in my SQL view, which is the source for the date date. As an example, if my date table starts at 1/1/2010, the month seq for Jan 2010 is 1 and the month seq for Jan 2011 is 13. The quarter seq for Q1 2010 is 1 and the quarter seq for Q1 2012 is 9.

http://www.daxpatterns.com/time-patterns/ is a good read for this topic.

SSAS DAX time intelligence previous year and calculation gives wrong value

1 Answers