5 Replies Latest reply on Feb 12, 2013 7:14 AM by Jeremy Riberdy

# Extract historical data dependant on current date

I am trying to find a way to use the date range filter to extract historical data based on a current date. Example:

The date filter range is from 1/1/2008 - 12/31/2012 (is visible and can be manipulated/filtered) - for this example let's use 1/1/08 as the start date and 12/1/2012 as the target end date

Data displayed is trend line for Customer ABC's revenue over time - for this example I see that Company ABC's revenue went to \$0 in December 2012

In an effort to see how muc revenue I lost I am trying to look back to see how much revenue was generated by Company ABC 8 months before it got to \$0, in an effort to guess at what the annual revenue lost might be (when they were in a "steady state").

I tried to use the formula DATEADD('month', -8, [DAY]) but all that did was timeshift all my data backwards by 8 months.

Is is possible to filter data using one date filter but then show these trend results by another date?

• ###### 1. Re: Extract historical data dependant on current date

You can use a table calculation to get the prior 8 months of sales on the same view. In that case, you'll probably want to use a table calc filter on the month so that way the results of the prior 8 months of sales don't change - a regular filter would be processed in the data source and would hide the needed data. I set this up in the attached.

Jonathan

• ###### 2. Re: Extract historical data dependant on current date

Jonathan - thank you for the quick response. This is much closer to what I am looking for but not quite there. Is it possible to just get the single value of the field 8 months ago instead of the cumulative amount? Could I use a formula like: "WINDOW_SUM(SUM([Sales]),-7,0)" minus "WINDOW_SUM(SUM([Sales]),-6,0)" to get that single amount?

• ###### 3. Re: Extract historical data dependant on current date

Just make the window calculation start and end value the same (-7).

WINDOW_SUM(SUM([Sales]),-7,-7))

or maybe this is a bit cleaner:

LOOKUP(SUM([Sales]), -7)

• ###### 4. Re: Extract historical data dependant on current date

Even easier: LOOKUP(SUM([Sales]),-8) would get you the value from 8 months ago.

• ###### 5. Re: Extract historical data dependant on current date

Thank you both! I will give these a try.