Tag Archives: Comparable days

Comparable day in DAX

Posted on by • Posted in Date functions • Tagged , , • Leave a comment

In many companies they want to compare todays sales with the comparable day last year. To find the comparable day we subtract 364 from todays date. In this sample todays date is 15 january 2007.

To see what dates we should get we can run this SQL

select '2007/01/15' as TodaysDate, DateName(dw, '2007/01/15') As 'DayName', 
dateadd(day, -364, '2007/01/15') As ComparableDay, DateName(dw, dateadd(day, -364, '2007/01/15')) As ComparableDayName

And this will return this result

Comparable day 3

To check the values for these dates we can run these DAX queries

EVALUATE
FILTER (
    ADDCOLUMNS (
	VALUES ('Date'[Date]),
        "Sales this day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

EVALUATE
FILTER (
    ADDCOLUMNS (
	VALUES ('Date'[Date]),
        "Sales this day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] = DATE ( 2006, 01, 16 )
)

The final query where we put all together looks like this

EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Comparable day", CALCULATE (
            'Internet Sales'[Internet Total Sales],
            DATEADD ( 'Date'[Date] , -364, Day)
        ),
        "Sales this day", 'Internet Sales'[Internet Total Sales]
    ),
    'Date'[Date] = DATE ( 2007, 01, 15 )
)

Comparable day 4

Please note that this sample requires a date dimension that has the date 2006/01/16. If you are unsure about this you will have to change the query. The -364 approach will have issues if last year was a leap years.

Comparable day in MDX using “lag” or “ParallelPeriod”

Posted on by • Posted in MDX • Tagged , , , , • Leave a comment

In many companies people want to compare todays sales with the comparable date last year. To find out what date the comparable day is we usually goes 364 days back from today.

In our sample today is 14 february 2012. And to see what the comparable date is we can use this query in SQL.

select '2012/02/14' as TodaysDate, DateName(dw, '2012/02/14') As 'DayName', 
dateadd(day, -364, '2012/02/14') As ComparableDay, DateName(dw, dateadd(day, -364, '2012/02/14')) As ComparableDayName

This will give this result

Comparable day 1

To check the sales for these days in MDX we can use these queries:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].&[20110215] ON 1
FROM [Adventure Works]

The final query that compares today sales can look like this (using “lag”)

WITH MEMBER [Sales comparable day] AS
  ( [Date].[Calendar].CurrentMember .Lag ( 364 ),
  [Measures].[Internet Sales Amount] ),
  FORMAT_STRING = "Currency"
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Or this (using ParallelPeriod and Aggregate)

WITH MEMBER [Sales comparable day] AS
  Aggregate (
    ParallelPeriod ( [Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember ),
    [Measures].[Internet Sales Amount]
  )
MEMBER [Percent change] AS
  [Measures].[Internet Sales Amount] / [Measures].[Sales comparable day],
  FORMAT_STRING = "Percent"
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Sales comparable day], [Measures].[Percent change] } ON 0,
[Date].[Calendar].[Date].&[20120214] ON 1
FROM [Adventure Works]

Comparable day 2

These samples can be run against the AdventureWorks cube in Multidimensional mode. To avoid error messages please make sure your time dimension has the members for the date you are comparing with. In this case 15 february 2011 has to exists in the time dimension. The -364 approach will have issues if last year was a leap years.