Prior Week Buyers

0
votes

I'm trying to calculate the number of buyers from a prior week based on the current week using Dax / power pivot (vs just the last 7 days). The current table I have looks like this

User | NB sales | Week Ending | Week Count | Date
Bob  | 10       | 08/28/2016  | 34         | 8/27/2016  
Tim  | 20       | 08/28/2016  | 34         | 8/26/2016
Bob  | 5        | 08/21/2016  | 33         | 8/20/2016
Joe  | 8        | 08/21/2016  | 33         | 8/19/2016
Jim  | 4        | 08/21/2016  | 33         | 8/19/2016
...

The result would look like the following, if the week in the pivot was 8/28/2016 

CurrentWeek | PriorWeekBuyers  | CurrentWeekCount  
8/21/2016   |       3          |        34   
...

I can't just subtract off week count as it won't work for week one of the new year, and there is no function for prior week. I'm hoping to make a pivot table with weeks that shows Buyer counts from previous week as a dax formula / measure.

Thanks

1
sqlexcelpowerpivotdaxexcel-2016

1 Answers

1
votes

Figured it out, with the following dax formula 

PrevWeekBuyers :=
CALCULATE (
    DISTINCTCOUNT ( Order[Customers] ),
    DATEADD ( Date[date], -7, DAY )
)

I then show a "week ending" date which rolls up the days in the correct order in the pivot table