I am attempting to create a weekly comparison of our monthly revenue forecasts on Engagements. Within each engagement the summary formula is doing it's job perfectly. The issue that I have is, with two row levels, the previous group values are comparing between Engagements where I rather they not (ie. Latest date from one project, compared to earliest date of next project). Is there a way to compare values of my dates rows without comparing them across the projects rows?
Current "Weekly Variance" summary formula: Firm Forecast:SUM - Prevgroupval(Firm Forecast:SUM, Snapshot Date, 1).
Row levels are 1: Project, 2: Snapshot Date.
Column level 1: Revenue Month Display Row: Snapshot Date
Display Column: Revenue Month
I am aware that I could swap the axis for both date fields and look at the data vertically. I could also move Engagements to the columns and look at them in a long horizontal list. Neither is acceptable to the users. I'm so close, I just need to keep the previous row comparisons within their own engagements! Thank you for any help!
For anyone that finds this: I found a partial solution by creating a "Snapshot Age" formula field on the snapshot object.
Snapshot Age = Today() - SnapshotDate
This allowed me to write an IF statement back in the report to compare between rows where the average previous SnapshotAge was greater. Otherwise it leaves a 0. (I sure wish we could set Null as a result)
I encountered another issue anywhere the previous row was blank. Without a value, the Snapshot Age defaulted to Null/Zero which tripped up my logic. I adjusted to only having it give me a variance if the Snapshot Age was within the last 7 days. At least it works for This Week vs Last Week.
