Another Previous Year value question

Question

• Hi

I hope someone can help.  I am new to DAX, but have managed to solve one part of my problem.  To demonstrate the problem, I will use examples from Adventure Works.

I need to calculate the sum(Sales Amount) for each period (format: yyyymm) of the last year.  The last year should be dynamic, determined by the last ORDER DATE in the Fact table (Internet Sales).  So in short I want the Total Sales amount per period for the last 12 months (Period Internet Sales ).  I got this part right with the following:

CALCULATE(Sum('Internet Sales'[Sales Amount]),DATESINPERIOD('Internet Sales'[Order Date],

CALCULATE(MAX('Internet Sales'[Order Date]),FILTER(ALL('Internet Sales'),TRUE)),-1,YEAR))

Now I have the problem that I want to compare these period values with sales values from the 12 months prior to the last year.  This means that if I have a sales value for period 200807, I want to compare it to the sales value in 200707.  My problem is that I can get the values, but cannot restrict or filter it to the same periods.

The prior period values (Internet Prior Period Sales  calculated by:

CALCULATE(SUM('Internet Sales'[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

How do I restrict INTERNET PRIOR PERIOD SALES to also just display for the same 12 periods as PERIOD INTERNET SALES?

Thanks

Thursday, July 11, 2013 9:14 AM

All replies

• Have you tried using PARALLELPERIOD to fetch the data from 2 years back?

```=CALCULATE(
SUM([SalesAmount]),
PARALLELPERIOD(Date[Date],-2, YEAR)
)```

Thursday, July 11, 2013 2:22 PM
• Hi Mike

I have tried it, but also doesn't give the correct result.

Thursday, July 11, 2013 3:25 PM
• You will probably find the formula you are looking for in the sample chapter of the book about PowerPivot for Excel 2010 you can download from this link:

http://www.sqlbi.com/books/powerpivot-for-excel-2010-give-your-data-meaning/

Maybe you are looking for the Moving Annual Total (see MatLineTotal measure in the chapter).

In case you are interested in the book, I'd suggest you considering the new one about Excel 2013, because 90% of the content is good also for Excel 2010 and it covers the last version available of PowerPivot for Excel 2010 (the one released with SQL Server 2012):

http://www.sqlbi.com/books/excel-2013-building-data-models-with-powerpivot/

Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

Thursday, July 11, 2013 3:58 PM