Is it possible to calculate 6 months of data for a metric?

You can do this in Magic ETL pretty easily with a formula tile, group by tile and a rank and window tile.

In the formula tile, use the LAST_DAY() function to normalize the dates in each month to the same day, like this: LAST_DAY(datefield)

In the group by tile, group by your last day field and do a distinct count of customer. This will give you a total for each month.

In the rank & window tile, give your field a name like 6-month total choose sum and choose your customer count field that you created in the group by tile. Enter 5 for preceding and 0 for following.

This will give you a 6 month total for each month, assuming you aren't missing any months.

Hope this helps.

@MarkSnodgrass i think this is logically flawed.

if you calculate the number of distinct customers in a month, you cannot add each month's number for a year.

i.e. 50 in jan, 35 in feb = 85 distinct customers.

you would be assuming that you had no repeat customers...

if your data is measured per month and you want a rolling 6 months, what you would could do is create a rolling window for each month that represents the appropriate set of dates.

i.e on Jan 21 i need a window spanning Jan 20 to Jan 21 (so use a formula tile to calculate last_day() for Jan 21 and then last_day for the month one year ago (date_diff).

now we need an aggregate table representing one row per month (table_a). so just GROUP BY date and MIN(date_lastYear).

then JOIN table_a onto the full activity table, table_b. Ideally you'd want to JOIN on table_b.date between table_a.date_lastYear and table_a.date.

but since Domo doesn't support JOIN on a BETWEEN clause you can do a cross apply. add a column to the tables _join_col = 1. then JOIN table_a to table_b on _join_col. then filter table_b.date between table table_a ...

Now you can calculate COUNT DISTINCT for table_b.customer_id.

You can now take this and join it back to your original activity table.