Skip to content

Year-To-Date Year-Over-Year Filter Options in Salesforce Reports and Dashboards

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”.

  1. First step: Go to the object that has the date field you want to use and create a new formula field.
  2. Name it something like “[your date field] Relative Time Periods”. Use the “text” data type.
  3. 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.
  4. 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).
  5. 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).
  6. 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)
  7. Copy the resulting formula from your text editor and paste it into the formula editor for the new field you are creating in Salesforce.
  8. 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!
  9. 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.

|0CYTD|0FYTD|-1Q|-2MTD|-13WTD|-91D|

  • 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.

Here’s an example of the settings for a dashboard filter value. Notice the “Contains” operator and that the “Value” is searching for a text string in the custom formula field set up earlier. The display text can be anything you’d like.
Here’s the dashboard filter at run time. Users can choose to see one of five FYTD comparisons, or look at the entirety of the previous 2 fiscal years. How easy is that!
The Time Frame filter is set to “Current and Previous FYTD” – which results in data being filtered to the current FYTD and the same FYTD time frame in the previous fiscal year. A grouping on the date field with the granularity set to “Fiscal Year” neatly shows the FYTD comparison.
Switching the Time Frame filter to “Current and Previous 2 FYTD” will add the data from the FYTD time frame from two fiscal years ago. Now all three FYTD data sets can be compared. What a lovely upward trend we are seeing!

Conclusion

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 View All

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.

14 thoughts on “Year-To-Date Year-Over-Year Filter Options in Salesforce Reports and Dashboards Leave a comment

  1. I am pretty new to this, but I want to create a report (or dashboard) that looks at a policy renewal date and compares it to today’s date and tells me what is coming for renewal in 60 or some number of days from today’s date. I do not see a way to do this is a standard Salesforce report, at least 9 months ago, but is there a recommendation on how to create something that will show what is up for renewal in xx days. Maybe make the xx days a user defined period or a drop down showing 60, 60, 90 days. Open to recommendations.

    Like

    • Hi John,

      If your records have a “policy renewal date” field you want to filter on dynamically using options like “Next 30 Days”, “Next 60 Days”, “Next 90 Days”, etc, you can use relative date filters: https://help.salesforce.com/articleView?id=sf.filter_dates_relative.htm&type=5

      For “records with a policy renewal date within the next 30 days” use a filter like:
      Policy Renewal Date equals “Next 30 Days”

      This is available in the Time Frame filter option within a report. If you just need 30, 60, 90, and 120 days, these are all available choices in the “Range” drop-down list. In the first 10 seconds of this video clip https://youtu.be/knMFtKamPUM?t=49 they are editing the time frame filter and choosing a range of “All Time”. This is where you will want to set the “Policy Renewal Date” as the “Date” field and choose “Next 30 Days” at the bottom of the “Range” choices. This “range” filter option can be edited on-the-fly after you run a report from the filter panel at the top-right of the screen.

      Or for “records with a policy renewal date exactly 30 days in the future” use 2 filters:
      Policy Renewal Date equals “Next 30 Days” (this can be set in the Time Frame Range, same as above)
      and
      Policy Renewal Date Greater Than “Next 29 Days” (this is a custom field filter using the Relative Date option)

      The same can be done for 60 days, 90 days, and any other time frame.
      To get the drop-down toggle experience for selecting from “Exactly X Days in the future” choices at run-time, you will want to set up these filtering options in a dashboard filter: https://help.salesforce.com/articleView?id=sf.dashboard_filters_editing.htm&type=5

      Like

  2. I’m still trying this. I’m having trouble with the source report I believe, with no example to go by. How do I use the field we created above (and I have got that! , it is called start date relative time periods)? Is that the filter? If I’m doing a report, to find #members joined per day, week, month, year, is the source report grouped by signup date and then use the new formula field as the filter on dashboard, or the other way around, or am I missing something else?

    Like

    • Hi Tracy,

      I would recommend setting up a source report using a report type that will show “Member” records on each row – I’m thinking this would be Contacts? Or let me know if you have a different object. Then group the data by “Signup Date”. From here, you can add a vertical bar chart right within the report that shows the number of records in each Signup Date grouping (which is grouped by day). Add any necessary filters you’d like (such as ensuring Signup Date is not blank), but be sure to leave the timeframe as “All Time”. Save this report as “Members Joined Per Day”

      Clone that report, then change the Group Date By option on the “Signup Date” field grouping to “Calendar Week”, as shown in this screenshot https://trailblazers.salesforce.com/answers?id=9063A000000aI21QAE Ensure the bar chart is using the new grouping granularity to show weeks. Save this report as “Members Joined Per Week”.

      Repeat the above step to clone the report 2 more times and change the Group Date By option to “Calendar Month” and “Calendar Year”, respectively. When you’re done, you should have 4 source reports that are identical except for the Group Date By option on the “Signup Date” field grouping.

      Add all four of these source report charts to a single dashboard. Then you can set up a Dashboard filter to narrow down the data to the timeframes you are interested in seeing. You can use the “Signup Date” as the field in the Dashboard filter if you simply need to see various combinations of whole timeframes like This Year, Last Year, Two Years Ago, This Month, Last Month, This Week, Last Week, etc. (read the next paragraph for more complex filtering) Here are instructions for adding a dashboard filter: https://help.salesforce.com/articleView?id=sf.dashboard_filters_editing.htm&type=5 and here are the “relative date” values you can use with date fields (“Last Month”) so you don’t have to hardcode specific calendar dates (12/1/2020 – 12/31/2020): https://help.salesforce.com/articleView?id=sf.filter_dates_relative.htm&type=5 This will make your dashboard filters dynamic so they work no matter when you run it.

      If you need filter options for more complex filtering of the overall timeframe on the dashboard, like “Current Year-To-Date vs Previous Year-To-Date”, that’s where the formula field in this article will come in handy. Use the “start date relative time periods” field as the field to filter on in the dashboard filter. Then add a filter value like:

      Contains |0CYTD, -1CYTD
      – display text: Current and Previous Year

      You can use both simple and complex filter values when using the “start date relative time periods” field as your dashboard filter:

      Contains |0CYTD, -1CYTD
      – display text: Current and Previous Year-to-Date

      Contains |0CY, -1CY
      – display text: Current and Previous Year

      Contains |0MTD, -1MTD
      – display text: Current and Previous Month-to-Date

      Contains |0M, -1M
      – display text: Current and Previous Month

      Hope that makes sense. If you are still having trouble, feel free to reach out to me at evan@pontersolutions.com – we can set up some 1:1 time

      Like

  3. This is so great, exactly what I was looking for. But my question is, when you make a filter, can you have one for months, one for year, one for week? Then does the source report need to be sorted by all those, or do I use different source reports? Let me reword. I’m to make a dashboard with # of new members per day/month/week/year. How do I sort the source report, and can it all be on one dashboard component with filters like you describe?

    Like

    • Hi Tracy,

      Let’s say all your dashboard charts show vertical bar graphs. The X-axis grouping options for each chart are set in the source report – After you group a date field, you can set the “Group Date By” option to “Day” or “Calendar Week” or “Calendar Month” or “Calendar Year”. Unfortunately, you won’t be able to toggle this setting from the dashboard on-the-fly via dashboard filter.

      BUT, what you could do to provide all these views on a single dashboard is clone your source report to create separate reports, then set each one to group the dates by the groupings you need to see (one report for days, one for months, etc). Then add a dashboard chart for each source report. Each chart will be showing the same data, but the granularity of date groupings will differ.

      Then add in dashboard filters using the formula field from above like:

      Contains |0CY,-1CY
      – display text: This and Previous Year

      Contains |0M,-1M
      – display text: This and Previous Month

      Contains |0W,-1W
      – display text: This and Previous Week

      Contains |0D,-1D
      – display text: Today and Yesterday

      Of course, you can set these filter options to any timeframes you’d like.

      People will be able to toggle to the time frame they want to see, then scroll down to the chart showing the date grouping granularity they want to see.

      Like

    • Unfortunately, no. This solution only works on objects where you can create a custom formula field. Neither Forecasts Quotas nor Forecasting Items allow for this right now.

      Like

  4. Evan – This is great. Do you have any way to show Current Quarter + 2, CQ + 3, etc. For example, sitting in Q4, I want to look at Q2 next year, CQ + 2. Salesforce’s default relative dashboard filters only allow you to view the Next 2 Fiscal Quarters which shows CQ + 1 and CQ + 2 combined. I’d like to see CQ + 2 in isolation and other future quarters as well. I’ve been thinking about this for awhile and can’t quite figure it out.

    Like

    • Hi Nick,

      Yes! This would be easy to configure. Once you set up the custom formula field on the object in question using the instructions in this post, you can add the following filter options to a dashboard filter set up to filter values from that custom formula field:

      Contains |2Q
      – display text: 2 Quarters From Now

      Contains |3Q
      – display text: 3 Quarters From Now

      Contains |4Q
      – display text: 4 Quarters From Now

      …etc

      You can also create new combinations like:

      Contains |3Q,|4Q
      – display text: 3 and 4 Quarters From Now

      And if you wanted to filter to the quarter-to-date for those time frames, just add the “TD” suffix after each “Q”

      Contains |2QTD
      – display text: 2 QTD From Now

      Hope this helps – let me know if you have other questions.

      Like

      • Evan – This is truly genius. I made one modification to make this work for my use case. I wanted to be able to see things in FQ + 1, FQ + 2, etc. Our fiscal starts 2/1. So, when we were at the end of Q1 in April, the relative date said that deals in May were 0FY0Q away. I modified the Quarter Offset Calculation. I had it subtract one from the calculation to adjust the quarter for my fiscal quarter dates. I did this using a formula field in case our fiscal year ever changes, per your advice. The revised formula works for my use case. Even while in April it shows deals in May as 0FY1Q which is perfect. Thought I’d share back.

        “|” /***** CALENDAR YEAR OFFSET CALCULATION *****/
        &
        TEXT(
        YEAR(CloseDate) – YEAR(TODAY())
        )
        &
        “CY”
        & /***** CALENDAR YEAR TO DATE INDICATOR *****/
        IF(
        MONTH(CloseDate) < MONTH(TODAY())
        ||
        (
        MONTH(CloseDate) = MONTH(TODAY())
        &&
        DAY(CloseDate) Fiscal_Year_Start_Month__c
        ||
        (
        MONTH(CloseDate) = Fiscal_Year_Start_Month__c
        &&
        DAY(CloseDate) >= Fiscal_Year_Start_Day__c
        ),
        1,
        0
        )

        (YEAR(TODAY())
        +
        IF(
        MONTH(TODAY()) > Fiscal_Year_Start_Month__c
        ||
        (
        MONTH(TODAY()) = Fiscal_Year_Start_Month__c
        &&
        DAY(TODAY()) >= Fiscal_Year_Start_Day__c
        ),
        1,
        0
        ))
        )
        &
        “FY”
        & /***** FISCAL YEAR TO DATE INDICATOR *****/
        IF(
        MOD(MONTH(CloseDate) + 12 – Fiscal_Year_Start_Month__c , 12) <
        MOD(MONTH (TODAY()) + 12 – Fiscal_Year_Start_Month__c, 12)
        ||
        (
        MONTH(CloseDate) = MONTH(TODAY())
        &&
        DAY(CloseDate) <= DAY(TODAY())
        &&
        (
        Fiscal_Year_Start_Day__c DAY(TODAY())
        )
        ),
        “TD”,
        “”
        )
        & /***** QUARTER OFFSET CALCULATION *****/
        TEXT(
        CEILING((MONTH(CloseDate)- Months_to_Offset_Quarters__c ) / 3) – CEILING((MONTH(TODAY())- Months_to_Offset_Quarters__c) / 3)
        +
        4 * (YEAR(CloseDate) – YEAR(TODAY()))
        )
        &
        “Q”
        & /***** QUARTER TO DATE INDICATOR *****/
        IF(
        MOD(MONTH(CloseDate)-1, 3) < MOD(MONTH(TODAY())-1, 3)
        ||
        (
        MOD(MONTH(CloseDate)-1, 3) = MOD(MONTH(TODAY())-1, 3)
        &&
        DAY(CloseDate) <= DAY(TODAY())
        ),
        "TD",
        ""
        )
        &
        "|"
        & /***** MONTH OFFSET CALCULATION *****/
        TEXT(
        MONTH(CloseDate) – MONTH(TODAY())
        +
        12 * (YEAR(CloseDate) – YEAR(TODAY()))
        )
        &
        "M"
        & /***** MONTH TO DATE INDICATOR *****/
        IF( DAY(CloseDate) <= DAY(TODAY()),
        "TD",
        ""
        )
        &
        "|"
        & /***** WEEK OFFSET CALCULATION *****/
        TEXT(
        (
        (CloseDate – WEEKDAY(CloseDate))

        (TODAY() – WEEKDAY(TODAY()))
        )
        /
        7
        )
        &
        "W"
        & /***** WEEK TO DATE INDICATOR *****/
        IF(WEEKDAY(CloseDate) <= WEEKDAY(TODAY()),
        "TD",
        ""
        )
        &
        "|"
        & /***** DAY OFFSET CALCULATION *****/
        TEXT(
        CloseDate – TODAY()
        )
        &
        "D"
        &
        "|"

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: