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
then you can use the value as you want
let me know if this works
2 of 2 people found this helpful
My solution differs a bit from Jim's, because I observed that you want to be able to do month-over-month 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 parameter-based 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.
IF [Order Date] < [Start Date] OR [Order Date] > [End Date]
ELSEIF [Order Date] < DATEADD('month', INT((DATEDIFF('month', [Start Date], [End Date])+1)/2), [Start Date])
I 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.
I wanted to give the max flexibility by going to days -
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!
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.
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.
If you're trying to compare quarter-over-quarter, 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.