LY Comparison

Hi all!
Im trying to calculate the difference between sales today and the same day of the week same week last year. So if the current date is 2019-06-13 (week 24 and Thursday) I want to compare it (the diff) with sales from Thursday week 24 LY which is date 2018-06-14. I would like to have this as a measure so I can plot this on a diagram with date on x-axis.

Any suggestions?

Edit: All data is in the same table - Date, Sales, other dimensions…

Cheers,
Cris

Coincidentally I just posted this same solution on another thread, it’s formatted for month but I’m sure could be changed to consider the week.

  dimension: period_comparison {
    case: {
      when: {
        sql: ${date} >= DATE_TRUNC('month', GETDATE())
          AND ${date} < DATE_TRUNC('day', GETDATE());;
        label: "current_month"
      }

      when: {
        sql: ${date} >= DATE_TRUNC('month', GETDATE() - interval '1 year')
          AND ${date} < DATE_TRUNC('day', GETDATE() - interval '1 year');;
        label: "last_year_same_month_same_point"
      }

      when: {
        sql: ${date} >= DATE_TRUNC('month',GETDATE()) - interval '1 month'
          AND ${date} < DATE_TRUNC('day', GETDATE() - interval '1 month');;
        label: "last_month_same_point"
      }

      else: "unknown"
    }
  }
2 Likes

Thanks! I changed it a bit and its working fine.