View the original community article here

Last tested: May 2020

The Problem

You want to give your users a single date selector, where they can choose a month. Then, when they select a month, they should see data from that month and the previous month.

Filtering for this month on a date dimension will remove any data from the past month, unfortunately. How to show both?

The Solution

Assume ${date_raw} is the date you want to filter on. (Make sure to use the raw timeframe if it's a dimension group!)

First, create a filter field for the user to interact with. Remember that a filter field doesn't immediately update the WHERE clause, but can be targeted with Liquid.

filter: date_selector { type: date} 

Second, create a yesno dimension that captures the value of date_selector using liquid. This will be your "this month" indicator.

dimension: is_selected_month { type: yesno sql: {% condition date_selector %} ${date_raw} {% endcondition %} ;;} 

Third, create a yesno dimension that captures the start and end of date_selector and subtracts a month. The SQL will vary by dialect.

dimension: is_previous_month { type: yesno sql: ${date_raw} > ADD_MONTHS(-1, {% date_start date_selector %}) AND ${date_raw} < ADD_MONTHS(-1, {% date_end date_selector %}) ;; 

Fourth, put these yesno's into filtered measures as desired.

measure: count_selected_month { type: count filters: { field: is_selected_month value: "yes" }}measure: count_previous_month { type: count filters: { field: is_previous_month value: "yes" }}