ARR Between Two Date Columns (Sub Start and End Dates)

Craig1234

I have three columns…

Subscription Start Date

Subscription End Date

Subscription ARR

Subscriptions are typically one year but not always. I want to chart ARR by month which is defined as the sum of all of the ARR for subscriptions active for each month.

X-Axis = Months ←- Not sure how to do this because if I pick Sub Start Date or End Date it will only sum the ARR for subs that end or start that month and not all of the months between Start and End

Y-Axis = Sum of all subscriptions active in that month.

GrantSmith

You’d need to explode your data so that you have a record for each month. You can do this with magicETL and filter where day = 1 to only get the first of each month. Then using a formula tile on your data you’d need to calculate the first of the month with something like

`Subscriotion Start Date` - INTERVAL (DAYOFMONTH(`Subscription Start Date`-1) DAY

 using the same formula tile just create a new column called Join and set the value to 1.

 On the date dimension dataset that Domo has from the Domo dimensions connector which you filtered on above fire day number 1 use another formula tile or
 tile create the same Join column with a value of 1.  Then inner join both on join = join. Feed that into a filter with a formula to filter where the dimension month is between your start and end dates

then you should have each record for the month it was active. You can then use the month date in your visualization

GrantSmith

You’d need to explode your data so that you have a record for each month. You can do this with magicETL and filter where day = 1 to only get the first of each month. Then using a formula tile on your data you’d need to calculate the first of the month with something like

`Subscriotion Start Date` - INTERVAL (DAYOFMONTH(`Subscription Start Date`-1) DAY

 using the same formula tile just create a new column called Join and set the value to 1.

 On the date dimension dataset that Domo has from the Domo dimensions connector which you filtered on above fire day number 1 use another formula tile or
 tile create the same Join column with a value of 1.  Then inner join both on join = join. Feed that into a filter with a formula to filter where the dimension month is between your start and end dates

then you should have each record for the month it was active. You can then use the month date in your visualization