Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you want on top of these fields, like Year Over Year, % Differences, etc. Aaaand one of the best parts is everything will update dynamically so you never need to touch them again!

**Desired output:**

Below you will find a list of common date comparison calculations. All references to the field ‘sales’ can be replaced with the metric you wish to display. If you dont want all calculatinos to be relative to today, aka *now()*, you can replace all references of *now()* with a date parameter like ‘${AsOfDate}’ which will allow the user to pick the date they want to anchor the ‘end date’ to. Or, if you want to base it of the latest date in your data set, then first create a calculated field like this and reference that date field as your end date instead:

Max Date:

`maxOver({your date field}, [ ], PRE_AGG)`

Between dateDiff(), addDateTime(), truncDate(), and extract() you can define whatever periods you want (and there are often multiple ways to do each one, these are just some proven examples).

**Today**:

`ifelse(dateDiff({order_date},now()) = 0,sales,0)`

**Yesterday:**

`ifelse(dateDiff({order_date},now()) = 1,sales,0)`

**Last 90 Days:**

`ifelse(dateDiff({order_date},now()) <=90, sales,0)`

**This week:** (Sun to today)

`ifelse(truncDate(‘WK’, {order date})= truncDate(‘WK’, now()) {order_date}<=now(), sales, 0)`

**Last Week:** (Last Sun to last Sat)

`ifelse(truncDate(‘WK’, {order date})= addDateTime(-1, ‘WK’, truncDate(‘WK’, now())), sales, 0)`

**Last WTD:** (Last Sun to today’s weekday of last week)

`ifelse(truncDate('WK', {order date})= addDateTime(-1, 'WK', truncDate('WK', now())) AND {order date}<=addDateTime(-1, 'WK', now()), sales, 0)`

**This year (aka Year to Date, YTD):**

`ifelse(dateDiff({order_date},now(),"YYYY") = 0 AND {order_date}<=now(), sales, 0)`

**Last year:**

`ifelse(dateDiff({order_date},now(),"YYYY") = 1, sales, 0)`

**Last year to date (aka LYTD):**

`ifelse(dateDiff({order_date},now(),"YYYY") = 1 AND {order_date} <= addDateTime(-1,"YYYY",now()),sales,0)`

**This month (aka Month to Date, MTD):**

`ifelse(dateDiff({order_date},now(),"MM") = 0 AND {order_date}<=now(), sales, 0)`

**Last month:**

`ifelse(dateDiff({order_date},now(),"MM") = 1, sales, 0)`

**Same month last year:**

`ifelse(dateDiff({order_date},now(),"MM") = 12, sales, 0)`

**Last month to date (aka LMTD):**

`ifelse(dateDiff({order_date},now(),"MM") = 1 AND {order_date} <= addDateTime(-1,"MM",now()),sales,0)`

**Month to date last year (aka LYMTD):**

`ifelse(dateDiff({order_date},now(),"MM") = 12 AND {order_date} <= addDateTime(-1,"YYYY",now()),sales,0)`

**This Quarter:**

`ifelse(dateDiff({order_date},now(),"Q") = 0, sales, 0)`

**Last Quarter:**

`ifelse(dateDiff({order_date},now(),"Q") = 1, sales, 0)`

**Same Quarter Last Year:**

`ifelse(dateDiff({order_date},now(),"Q") = 4, sales, 0)`

**MoM % Diff:**

`(sum({This Month}) - sum({Last Month})) / sum({Last Month})`

**YoY % Diff:**

`(sum({This Year}) - sum({Last Year})) / sum({Last Year})`

**Monthly YoY % Diff:**

`(sum({This Month}) - sum({This Month Last Year})) / sum({This Month Last Year})`

**QoQ% Diff:**

`(sum({ This Quarter}) - sum({Last Quarter}))/sum({Last Quarter}))`