How to create a 'days prior to event date' dimension, to be used in place of actual calendar days?

[data_guru]

I have a scenario where I want to compare the a the trend among multiple customer/clients leading up to a certain event date. To add some color, let’s consider the event of a subscription contract termination date.  I want to compare the platform usage trend for all customers, for the 12 months prior to their termination date.

Now, the termination dates and subscription dates/lengths between clients will differ so plotting their usage against a regular dates/calendar table will not line up nicely.

However I’m looking to align each customer’s trend by creating a dimension that calculates:

• 1,2,3,4,5,6… days prior to termination
• 1,2,3...52 weeks prior to termination
• 1,2,3….12 months prior to termination

I figure if I create a date dimension such as number of days, weeks, etc prior to termination, that sort of ‘common sizes’ the time range for everyone and I can overlay ever customer’s usage trend prior to termination and get a visual if there’s some correlation or if it’s just spaghetti!

To be clear, I envision plotting the number of days prior to even date on the X axis, and usage/hours/whatever else is a measure on the Y axis.

Question is if anyone has done this or put together such a ‘days prior/since’ dimension and/or how would you go about doing so. Could use some guidance which I would hope would also be helpful to others in various analysis!!

Thank you!!!

[IanT]

You just need to create a dimension which calculates the date diff in days between the activity you want to analyse and the milestone date ( activation/termination)