Many organizations have a handful of key performance indicators that they would like the see calculated for various time frames. Sometimes the current month is the focal point. Other times the current year to date is desired. Often there is a desire to compare the current year to date (or fiscal year to date) to the same time frame during the previous year. What about quarters? Weeks? The list goes on… but you don’t have to build a separate report or dashboard for every single combination of KPI and time frame. Instead, you can build a single dashboard along with the necessary source reports and allow the running user to choose their time frame with the ease and simplicity of selecting an option from a drop down menu. Up to 50 time frame options can be predefined using this method and you have complete control over the display values for those options. Here are the building blocks for this solution.
Create a formula field
A text-based custom formula field defined on the object itself is the key to this configuration. This will reference one of your date fields in order to perform various calculations and return a text string. Each calculation in that text string is determining how offset the date value is from the current date. This text string result is used in the report or dashboard filter, utilizing a “contains” operator, in order to give you the filter operation you need. Since all these calculations are relative to the current date, I’ve been calling this “Relative Time Periods”.
- First step: Go to the object that has the date field you want to use and create a new formula field.
- Name it something like “[your date field] Relative Time Periods”. Use the “text” data type.
- You can choose the time periods that you want to perform calculations for.
- I’ll show a fairly robust solution you can use as a starting point – calculating offsets for fiscal years, calendar years, calendar quarters, calendar months, United States weeks (Sun – Sat), and days.
- Each calculation is going to be surrounded by a delimiter (which helps you decipher certain values since we plan to use a “contains” operator later). I am using pipes (|) as my delimiter – feel free to use anything that’s not a letter or number or short dash/negative sign (-) if you don’t like pipes.
- Each offset calculation determines how “offset” your date value is from today. If the date is within the current time period, then there is 0 offset. If the date occurred in the previous time period, then there is -1 offset. If the date occurred two time periods ago, then there is -2 offset. Positive numbers will be used for dates in the future.
- Each relative time period calculation will also include a “to-date” indicator (except for the “Days” offset calculation). These act like checkboxes within the text string so you can target “to-date” time periods (like previous calendar year to date) or whole time periods (like previous calendar year).
- Copy the formula in the “Here is the code for the formula” section and paste it into your favorite text editor that supports a “replace” function (like notepad).
- Open the “replace” function window (typically Ctrl-H or Cmd-H)
- You will need to replace every instance of Date__c with the API name for the date field you’d like to use. Once you are sure you have the correct API name, go ahead and select “Replace All”
- You will need to replace every instance of Fiscal_Year_Start_Month with the number of the month when your fiscal year starts (e.g. April would be 4). I highly recommend storing this information in a custom setting or custom metadata type so you don’t have hardcoded magic numbers in all your formulas. Also it would be terrible for your organization to be bought by another organization that imposes a different fiscal year and you have to go update this number in hundreds of formulas. If you don’t have a fiscal year that differs from your calendar year, use 1 (which signifies January)
- You will need to replace every instance of Fiscal_Year_Start_Day with the number of the day when your fiscal year starts (e.g. April 15th would be 15. July 1st would be 1). I highly recommend storing this information in a custom setting or custom metadata type so you don’t have hardcoded magic numbers in all your formulas. Also it would be terrible for your organization to be bought by another organization that imposes a different fiscal year and you have to go update this number in hundreds of formulas. If you don’t have a fiscal year that differs from your calendar year, use 1 (which signifies the 1st of the month)
- Copy the resulting formula from your text editor and paste it into the formula editor for the new field you are creating in Salesforce.
- Give this field a lovely description. Feel free to steal mine:
- Formula field that calculates the Fiscal Year, Calendar Year, Quarter, Month, Week, and Day Offset of REPLACE_WITH_DATE_FIELD_NAME from Today. Thanks ReportForce.blog!
- Save the field, giving read access to profiles as necessary. This does not need to be on any page layouts, but you might decide to include it on one for testing purposes.
Here is the code for the formula
"|" /***** CALENDAR YEAR OFFSET CALCULATION *****/ & TEXT( YEAR(Date__c) - YEAR(TODAY()) ) & "CY" & /***** CALENDAR YEAR TO DATE INDICATOR *****/ IF( MONTH(Date__c) < MONTH(TODAY()) || ( MONTH(Date__c) = MONTH(TODAY()) && DAY(Date__c) <= DAY(TODAY()) ), "TD", "" ) & "|" /***** FISCAL YEAR OFFSET CALCULATION *****/ & TEXT( YEAR(Date__c) + IF( MONTH(Date__c) > Fiscal_Year_Start_Month || ( MONTH(Date__c) = Fiscal_Year_Start_Month && DAY(Date__c) >= Fiscal_Year_Start_Day ), 1, 0 ) - (YEAR(TODAY()) + IF( MONTH(TODAY()) > Fiscal_Year_Start_Month || ( MONTH(TODAY()) = Fiscal_Year_Start_Month && DAY(TODAY()) >= Fiscal_Year_Start_Day ), 1, 0 )) ) & "FY" & /***** FISCAL YEAR TO DATE INDICATOR *****/ IF( MOD(MONTH(Date__c) + 12 - Fiscal_Year_Start_Month , 12) < MOD(MONTH (TODAY()) + 12 - Fiscal_Year_Start_Month, 12) || ( MONTH(Date__c) = MONTH(TODAY()) && DAY(Date__c) <= DAY(TODAY()) && ( Fiscal_Year_Start_Day <= DAY(Date__c) || Fiscal_Year_Start_Day > DAY(TODAY()) ) ), "TD", "" ) & "|" /***** QUARTER OFFSET CALCULATION *****/ & TEXT( CEILING(MONTH(Date__c) / 3) - CEILING(MONTH(TODAY()) / 3) + 4 * (YEAR(Date__c) - YEAR(TODAY())) ) & "Q" & /***** QUARTER TO DATE INDICATOR *****/ IF( MOD(MONTH(Date__c)-1, 3) < MOD(MONTH(TODAY())-1, 3) || ( MOD(MONTH(Date__c)-1, 3) = MOD(MONTH(TODAY())-1, 3) && DAY(Date__c) <= DAY(TODAY()) ), "TD", "" ) & "|" /***** MONTH OFFSET CALCULATION *****/ & TEXT( MONTH(Date__c) - MONTH(TODAY()) + 12 * (YEAR(Date__c) - YEAR(TODAY())) ) & "M" & /***** MONTH TO DATE INDICATOR *****/ IF( DAY(Date__c) <= DAY(TODAY()), "TD", "" ) & "|" /***** WEEK OFFSET CALCULATION *****/ & TEXT( ( (Date__c - WEEKDAY(Date__c)) - (TODAY() - WEEKDAY(TODAY())) ) / 7 ) & "W" & /***** WEEK TO DATE INDICATOR *****/ IF(WEEKDAY(Date__c) <= WEEKDAY(TODAY()), "TD", "" ) & "|" /***** DAY OFFSET CALCULATION *****/ & TEXT( Date__c - TODAY() ) & "D" & "|"
Wait, what’s going on in there?
If you are panicking because of the length, it’s really just a bunch of small, simple calculations strung together. Check out the slide deck below for a breakdown of the logic using pictures. You’ll also want to reference the function documentation to understand how each part is calculated.
Here’s an example string based on “today” being July 31, 2019 and the date value on the record being May 1, 2019. This fiscal year for this organization starts on April 1.
- 0 calendar years ago, year-to-date
- 0 fiscal years ago, fiscal-year-to-date
- 1 quarter ago, not quarter-to-date
- 2 months ago, month-to-date
- 13 weeks ago, week-to-date
- 91 days ago
Putting this to use
You’ll get the best experience from this solution by employing it on a dashboard. First create the source report you need in order to show the KPI (or multiple KPIs) that you are interested in. But be sure to leave the time frame set to “All Time” for the date field being used in your relative time periods formula. Or you may have a situation where you want the “All” option on the dashboard filter to show a certain time frame (like the last five years) – in which case you can filter your source report to fit that requirement.
The groupings in your report need to reflect your use case. I wish I could be more specific here, but the scenarios are endless – some organizations want to see YoY analysis for a bunch of different time frames, so having a “Calendar Year” or “Fiscal Year” grouping on the source report will make the most sense. Even if you are comparing a single month to the same month in the prior year, you can still use a yearly grouping option – the filter on the dashboard will make sure that you are only looking at the single month from each year. In some cases, it may make sense to have two groupings using the same date field in your source report – one showing a yearly granularity and the other showing a monthly granularity. You can set up a formula field on the object that simply references your date field (or use a row-level formula in the report, though performance may suffer) and now you can group by this field a second time. On the dashboard, you can create a component for the yearly grouping and a second component for the monthly grouping.
The dashboard filter is the final step. Add a new dashboard filter and set it to use your new relative time frame formula field. Add filter values that specify the relevant time frame you want to filter down to when selecting that option. You will make heavy use of the “Contains” operator here. Here are some common filters you can use:
- Contains |0FYTD
- Display Text: Current FYTD
- Contains -1FYTD
- Display Text: Previous FYTD
- Contains |0FYTD, -1FYTD
- Display Text: Current FYTD and Previous FYTD
- Contains -1FY
- Display Text: Previous Fiscal Year
- Contains -2FY
- Display Text: 2 Fiscal Years Ago
- Contains -1FY, -2FY
- Display Text: Previous 2 FY
- Contains |1FY
- Display Text: Next FY
Note that the delimiter (the pipe character) is used to ensure we don’t confuse a 0 offset with a 10 offset, or a 1 with an 11, when looking at current or future dates. The negative sign serves this purpose when looking at past dates. You can mimic this pattern to create filters for calendar years, quarters, months, weeks, or even days. If you want to include additional time frames in one filter option, just keep adding commas. Here’s one for the previous five calendar years
- Contains -1CY, -2CY, -3CY, -4CY, -5CY
- Display Text: Previous 5 Calendar Years
Here’s a filter for month-to-date for the current and previous 6 months
- Contains |0MTD,-1MTD,-2MTD,-3MTD,-4MTD,-5MTD,-6MTD
- Display Text: Current and Previous 6 MTD
Once your filters are set up, you can start toggling the time frame for your data on-the-fly. This example dashboard has options for comparing the current FYTD to the same time frame in any number of the previous 5 fiscal years.
With so much flexibility in the dashboard filter settings, you can dial this solution in to solve a myriad of configurations. The best part is, it only consumes a single dashboard filter. The other two dashboard filters can be utilized for other filtering needs.
You can also make use the formula field in report filters to easily add FYTD, QTD, or MTD filters. Just ensure the formula field contains for the text string you need to see, and your data will be filtered accordingly. No longer do you have to create multiple checkbox formula fields on an object for each date that needs to be filtered in special ways.
What use-cases do you have for complex time frame filters? Adding this tool to your toolbox should help you achieve your next report task and easily “wow” your users.
Evan Ponter is a Salesforce Admin Hero from Baltimore, MD who has been focusing on declarative development since 2012. His desire to keep an org simple, streamlined, and maintainable by future admins has led him to being an expert on the declarative features of the platform. A deep understanding of reports, the importance of proper data modeling, and the utilization of declarative automation tools have propelled Evan along a blossoming Salesforce journey where he solves complex problems using clever solutions that provide the ultimate flexibility. When he's not logged into Salesforce, Evan enjoys playing bass guitar in a local rock band.