If you take data that has date and time and goes back several years and plot it on a chart, the current year is likely to lag behind in numbers, because unlike other years, it won't have data for all 12 months. This is where the so-called Year-on-Year or YoY reports come in handy.
To prepare your data for a YoY report, you will have to discard all the entries from the current point in a year until the end of the year, for every year.
For example, if you're in June 14th 2023 18:05:52 and your data goes back to 2017, you will have to discard the entries from:
- 2017-06-14 18:05:52 to 2017-12-31 23:59:59
- 2018-06-14 18:05:52 to 2018-12-31 23:59:59
- 2019-06-14 18:05:52 to 2019-12-31 23:59:59
- 2020-06-14 18:05:52 to 2020-12-31 23:59:59
- 2021-06-14 18:05:52 to 2021-12-31 23:59:59
- 2022-06-14 18:05:52 to 2022-12-31 23:59:59
This will make every previous year comparable to the current.
You can accomplish this using Calculated Fields.
Create a calculated field in your data source with the following formula (where event_datetime is your custom column name):
CURRENT_DATETIME() > DATETIME_ADD(
event_datetime,
INTERVAL (YEAR(CURRENT_DATE()) - YEAR(event_datetime)) YEAR
)(Set the data type of the new field to Boolean.)
The formula compares CURRENT_DATETIME() to values from event_datetime brought to the current year (leap year accounted for). If the resulting date and time is in the future, it should not be included into the Year-on-Year report.
Side Note: getting "Out-of-range datetime field in parsing function. Error a089525e"? Check if you're passing a valid date to PARSE_DATETIME(). If you pass a date that does not exist – e.g. 2021-02-29 – you will get the above error.
References: