Looking to compare the same measure across two different time periods in one explore

Hi All,

I am looking to compare Junes sales vs May sales broken down by a dimension, but specifically having a new measure that returns the difference between the two measures.

I have tried playing around with Table Calculations & the new create dimension/measure but I am not sure on the best way to approach this.

I want to avoid using lookml if possible as it’s an ad-hoc view.

Example of how I would want my end result to look


Daniel, it’s precisely for variance/growth calculation reasons that I decided that writing in LookML was the more appropriate approach for date calculations. Variance and growth rates are almost certain to come up in conversations with business users, so I like having them always accessible. I also have seen people use incorrect formulas for growth, so feel that that room for error can be easily eliminated (and therefore, it should).

Here’s my process:

  1. Implement the Date Comparison Block extend, and apply a view_label to each of the visible fields to keep them organized. I used ‘Timeline Comparison Fields’. A bonus - I can’t think of a better way to demonstrate the huge value of extends. Date Comparison Block
  2. On your Explore base, add individual Measures for two comparison periods, with filters applied so that they’re picking up values from the appropriate time buckets (referencing the ‘This Period’/‘Prior Period’ results). This will be measures like ‘Sales, Period 1’, or ‘Profits, Period 2’. I then put a view_label on those fields to show them within the same view as my Timeline Comparison Fields.
  3. Also on your Explore base, add Comparative fields for Variance and Growth and place them within your Timeline Comparison Fields.

At the end of all this fun, you get a robust date comparison extend that can be tacked onto any view with a date field with relative ease, and you’ll have something like the below: