1 of 1 people found this helpful
I think the best way to achieve this is by pivoting the two date fields you're comparing. I don't know of a way to compare it using the existing data structure, though someone else may chime in with a creative solution.One major caveat is this will restructure your data in a way that is sure to affect aggregations elsewhere, so I'd recommend making this its own data source for this specific purpose.
Here's how that workst:
1) Pivot the two date fields, like this:
2) Build your view and running sum, then perform a secondary calculation on that running sum, as shown here:
The attached workbook has the full example for you to look at.
Thanks for the help! The pivot really helps. How did you address the null values as not all data points have a closing date? My graph either starts at a negative number.
The link you provided doesn't seem to have your changes. Would please attempt to attach it again?
Sorry about that. I have modified the attachment above. To deal with the closing date being null, you can use a calculated field and put it on the filter shelf. Here's the calculation I used:
[Pivot field names]="Closing Date" AND ISNULL([Pivot field values])