I have a table with date in the following format:

orderID | dateOrdered | customerID 

101 | 01/01/2015 | 1
102 | 02/02/2015 | 2
103 | 05/06/2015 | 3 


I need to create a measure that tells me how many customer's placed an order in the previous 91 days of any given date, not necessarily today. 

So, if you imagine a PivotTable which has a series of dates down the left hand side and 'totalNoCustomersLast60Days' as the only column, I need each day to tell me how many customers ordered in the x number of days.

For example if we say 60 days my table would look like the following:

Each row in this table would be telling me that "x number of customers placed orders within the period 60 days prior to,and including, the date on the left".

I have tried numerous ways of doing this and despite thinking it should be incredible simple it eludes me.

Any help is much appreciated as always. 

• Changed type Michael AmadiModerator Tuesday, July 28, 2015 7:55 PM This thread is based on a question.

Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
With Earlier() and DistinctCount()
http://www.mediafire.com/view/25sco5f5zrtu1bp/07_23_15.xlsx

• Proposed as answer by Michael AmadiModerator Tuesday, July 28, 2015 7:55 PM

Thanks Herbert  - that's brilliant and has worked. I've spent about 2 weeks trying to sort that out much to the frustration of my manager!

Don't fully understand it all but will break it down bit by bit when I get a chance. 

I do have one further question which is that we want to use this same calculation but set different periods:

For example previous 7 days, previous 30 days, previous 365 days etc - what is the best way of doing this as I assume adding a column for each would be inefficient. 

Thanks. 

• Edited by Maracles Tuesday, July 28, 2015 5:31 PM

Maracles,

According to your description, you need to return the number of customers placed orders within the period 60 days prior to,and including, the date on the left, right?

This is called moving sum in PowerPivot. The sample DAX expression is looks like below.
CALCULATE([NoCustomer],
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-60, day
                       )
         )

Please refer to the link below to see the details.
http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/

Regards,