# Comparable day in DAX

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',
```

And this will return this result To check the values for these dates we can run these DAX queries

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

EVALUATE
FILTER (
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 (
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 )
)``` 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”

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',
```

This will give this result To check the sales for these days in MDX we can use these queries:

```SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].& ON 1

SELECT [Measures].[Internet Sales Amount] ON 0,
[Date].[Calendar].[Date].& ON 1

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].& ON 1

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].& ON 1 