Pulling year over year data in a side by side format

[jyau]

Has anyone found a clean, simple way to chart year over year transactional data, with this year as one line and last year as another line?

Or, in table format:
Date, Revenue This Year, Revenue Last Year
… for 30 days of data

I’d like to be able to only show 30 rows of data, not 395 (365 + 30) rows of data, which is part of the difficulty.

I’ve been able to solve this with a nasty hack, but am looking for cleaner solutions. My solution is below. Would appreciate any improvements!

The overall idea is to adjust the date (in my case, “completed_date”) to a date in the last 365 days. If the date was in the last 365 days, it’s not adjusted at all. Otherwise, it’s adjusted a corresponding amount. The example below is slightly more complicated, because I’m actually adjusting by only 364 days, to maintain comparisons of Mondays to Mondays.

For filtering:

  - dimension: completed_month_day_dow_adj_to_current_year
    type: time
    timeframes: [date, day_of_week]
    format: 
    sql: |
      DATE_ADD(${TABLE}.completed_at, INTERVAL 364 * FLOOR(DATEDIFF(CURDATE(), ${TABLE}.completed_at)/364) DAY)

I also created a separate dimension specifically for the label that adds the day of week, so it’s easier to read the data:

- dimension: completed_month_day_dow_adj_to_current_year_label
    type: string
    sql: |
      DATE_FORMAT(DATE_ADD(${TABLE}.completed_at, INTERVAL 364 * FLOOR(DATEDIFF(CURDATE(), ${TABLE}.completed_at)/364) DAY), '%Y-%m-%d (%a)')

With this dimension in place, I use this new dimension as my main dimension, pivot on completed_year, and add a measure (e.g. revenue). The filters are the key to eliminating all of the extra data between 30 days and 365 days out.

completed_at: in the past 400 days
completed_month_day_dow_adj_to_current_year: in the past 30 days

This combination of filters returns 60 rows of data, that are then pivoted on year, putting the data side by side.

The obvious downside to this approach is that you can only do easy comparisons of periods in the last 365 days. Spanning the 1 year mark doesn’t work. Also, a different hack is needed if you also want to be able to include future dates on your table (for example, if you want to put the next 7 days also on your table to see what to expect). Without a separate hack, future dates are also mapped to the last 365 days, which means tomorrow ends up with a date 364 days ago.

Would love to find a cleaner solution to this problem. Hopefully I’ve missed a very simple one!