
1. Re: How to show % change over varying time periods
Jim Dehner Jan 18, 2017 11:43 AM (in response to Adam Powell)See the attached solution
First calculate the number of days at the mid point between start and finish dates
Int(DATEDIFF('day',[Start Date],[End Date])/2)
Then convert that to a date
DATEADD('day',[days diff],[Start Date])
Then determine the sales in the first half (Numerator)
if([Order Date] >= [Start Date] AND [Order Date] <= [date at mid point]) Then [Sales] else 0 end
and second half sales (denominator)
if([Order Date] >= [date at mid point] AND [Order Date] <=[End Date]) Then [Sales] else 0 end
and then the % growth
(sum([Numerator])/sum([Denominator]))
then you can use the value as you want
let me know if this works
Jim

2. Re: How to show % change over varying time periods
Jamieson Christian Jan 18, 2017 11:59 AM (in response to Adam Powell)2 of 2 people found this helpfulAdam,
My solution differs a bit from Jim's, because I observed that you want to be able to do monthovermonth comparisons. In that case, splitting the reporting periods at the day level may not give you quite what you want. My solution assumes that your granularity will be no lower than the month, but then the rest of the calculations are essentially the same as how Jim approached it.
The core of my solution is the calculated field [Reporting Period], which contains 1 or 2 to indicate if the sale falls in the first half or second half of the parameterbased reporting range. (And 0 if it falls outside of the range, so we can filter those out conveniently.) Note: this field also obviates the need for your [Date Range] boolean calculation.
[Reporting Period]
IF [Order Date] < [Start Date] OR [Order Date] > [End Date]
THEN 0
ELSEIF [Order Date] < DATEADD('month', INT((DATEDIFF('month', [Start Date], [End Date])+1)/2), [Start Date])
THEN 1
ELSE 2
ENDI used a table calc filter on [Reporting Period] to show only "2", which is the second half of the reporting period, while retaining all of the underlying data that is needed to compare it to the previous reporting period "1".
See my attached workbook. The second tab is my solution, and you can change the parameters to see how it reacts.

3. Re: How to show % change over varying time periods
Jim Dehner Jan 18, 2017 12:06 PM (in response to Jamieson Christian)Nice solution
I wanted to give the max flexibility by going to days 
Jim

4. Re: How to show % change over varying time periods
Jamieson Christian Jan 18, 2017 12:11 PM (in response to Jim Dehner)Jim,
Agreed. There are caveats to both approaches.
 My approach will set an inappropriate midpoint if the selected period spans an odd number of months.
 Your approach will set an inappropriate midpoint if comparing e.g. March (31 days) to February (28 days).
It's good for Adam to see both approaches and the pros/cons of each!

5. Re: How to show % change over varying time periods
Adam Powell Jan 18, 2017 12:56 PM (in response to Jim Dehner)Jim,
Looks like growth % is only returning positive values. I would like to show when there is negative growth as well, but this is getting me very close! Thank you.

6. Re: How to show % change over varying time periods
Adam Powell Jan 18, 2017 1:05 PM (in response to Jamieson Christian)Jamieson,
This looks great. The % change is updating properly when I update the parameters. This is what I was looking for. Much appreciated.
You were correct in noting that it will not work if comparing an odd number of months. I will dive in and try to find a solution to that so, that when looking at a Quarter, it will work.

7. Re: How to show % change over varying time periods
Jamieson Christian Jan 18, 2017 1:07 PM (in response to Adam Powell)Adam,
If you're trying to compare quarteroverquarter, my solution will work fine, because the total # of months will be 6 (and the midpoint will be correctly placed 3 months in).
If you try to compare first half of a quarter with the second half of the quarter, then my solution will go wonky, because the total # of months will be 3.
You can certainly augment the formula to behave differently if it detects different types of time ranges in the parameters. It will get pretty messy looking, but totally doable.

8. Re: How to show % change over varying time periods
Jim Dehner Jan 18, 2017 1:37 PM (in response to Jim Dehner)right you are  corrected the formula and it should work now
Jim