create year-to-date (YTD) and month-to-date (MTD) (Community)

View the original community article here

Last Tested: Sep 21, 2018

This assumes date dimensions already exist in view. The solution requires SQL with Lookml.

Essentially, you'll want to create a yesno dimension that returns YES if date is before today

MTD Comparison (MySQL):

dimension: is_before_mtd {
type: yesno
sql: EXTRACT(Month from ${date_date}) < EXTRACT(Month from CURRENT_DATE);;
}

YTD Comparison (MySQL):

dimension: is_before_ytd {
type: yesno
sql: DAYOFYEAR(${date_date}) < DAYOFYEAR(CURRENT_DATE);;
}

This will return YES when dates are before the current date.

You can then filter on the YES to do analysis. Make sure to include the month timeframe!

Same concept can be carried out for YTD comparison. For more information check out this community article!