PowerPivot - How to calculate trend by comparing two periods?

Question

I'would like to create a KPI to compare results from two periods.

My data source looks like the following example :

User | NB sales | Date
Bob  | 10       | 01/01/2014
Tim  | 20       | 01/01/2014
Bob  | 5        | 01/02/2014
...

So I would like to compare the number of sales for the current to the previous week. However I don't know how to process it.

= CALCULATE(SUM('Sales [Nb Sales]'), WEEKNUM('Sales [Date]') = )

I seen on Microsoft help there is a DAX function ParallelPeriod() to compare periods but I don't know if I should use it.

Should I use the formula as calculated column or as calculated value (bottom part).

This KPI should indicate if an employee sold more or less products than the previous week.

Thanks,

Answer 1

I would probably handle this in 3 measures:

[Sales] = SUM(Sales[Nb Sales])
[Sales Prior Week] = CALCULATE([Sales], DATEADD(Sales[Date], -7, Day))
[Sales WOW] = ([Sales] / [Sales Prior Week])-1

You should really be using a separate, linked, date table for all the calendar stuff!