Creating a measure that looks only at the previous x number of days

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. 







July 20th, 2015 7:28pm

Create a measure, Customers:=COUNT([customerID])

Go to Power View or pivot table and click Customers, it will give you a total count. Drag dateOrdered into the Filters area and filter by the last 90 days. Now you have the count of the customers that have ordered in the last 90 days.

You could improve on this by creating a calculated column that calculates the number of days between TODAY() and [dataOrdered], =1. * TODAY()-[dateOrdered] and then you filter by this value <= 90 instead

To get the table above, put date and Customers measure in a table and filter the table by last 90 days or 60 days or what not.


  • Edited by Seth Moupre Wednesday, July 22, 2015 2:39 PM
Free Windows Admin Tool Kit Click here and download it now
July 21st, 2015 2:35am

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

July 23rd, 2015 8:07pm

Sorry, I've been away but will look at your suggestion now. Thanks. 
Free Windows Admin Tool Kit Click here and download it now
July 28th, 2015 12:15pm

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 13 hours 36 minutes ago
July 28th, 2015 1:46pm

I recommend adding columns.
Why do you assume it is inefficient?
If you want previous days as an external parameter,
here is one way:
http://www.mediafire.com/view/f3whfxfhqf6qkez/07_28_15.xlsx
Free Windows Admin Tool Kit Click here and download it now
July 28th, 2015 6:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics