Skip to content
September 30, 2011 / Erik Svensen

Parallel Week in DAX

Unfortunately none of the Time intelligence functions in DAX supports the interval “week” in order to get the same week last year.

In my case the data comes by week and I use the monday as the date key for the sales.

So the 3-1-2011 is a Monday

Sales Amount LY:=CALCULATE(SUM(FactStoreSales[SalesAmount]), PARALLELPERIOD(DateTime[Date],-1,year))

This means the this DAX expression will point to 3-1-2010 – but week 1 2010 actually starts with 4-1-2010.

In order to solve that I changed my expression to

Sales Amout LY:=CALCULATE(SUM(FactStoreSales[SalesAmount]), DATEADD(DateTime[Date],-364,DAY))

That did the trick J

Be aware that this means that years following years with 53 weeks – week 1 will be matched against week 2 in the previous year.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: