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”.
- 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.
|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.
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.
Categories
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.
ReportForce 
Thank you. If I need to sum/cumulative sum of opps/leads by the calendar date (not by created date/closing date) is there a way without creating an object in salesforce? Like I want to see the total opporunities open by jan, feb, mar etc..
LikeLike
Ajith – you can try building a report using the “Opportunity Trends” report type (https://help.salesforce.com/s/articleView?id=sf.reports_opp_trends.htm&type=5). This standard functionality is capturing a snapshot of opportunity data at 0:00 GMT on the last day of every month for the following month.
LikeLike
Hi again, is there any option to compare same period this year and previous year, but I need data from yesterday from January 1st 2021 to yesterday day in 2021 (April 7th 2021) and compare with January 1st 2022 to yesterday (April 7th 2022) ?
LikeLike
Hi Jose, in your formula field for relative time frames, you can add the following logic to the end:
&
IF(MONTH(Bill_Date__c) = MONTH(TODAY()) && DAY(Bill_Date__c) = DAY(TODAY()),
“TODAY IN ANY YEAR”,
/*ELSE*/
“”
)
Then when filtering, ensure [the formula field] does not contain TODAY IN ANY YEAR
LikeLike
Hi! Firstly, thanks for you great job. I´m using your formula in a report. Can I get data until YESTERDAY? How can I get 0CYTD but until Yesterday? Thanks in advance
LikeLike
Hi Jose – you can set up one report filter as [the formula field] contains |0CYTD. Then set up a second report filter, select the source date or date/time field, choose “less than” for the Operator, and enter “TODAY” for the value. Alternatively, your second report filter could be [the formula field] does not contain |0D
LikeLike
Hi Evan, thanks so much for your help and great job. Now is working good.
I used the comparative -1CYTD,0CYTD and Bill date less than TODAY.
LikeLike
Thank you Evan! Appreciate the work you put into this. I am getting a syntax error on entering the formula “Error: Incorrect parameter type for function ‘YEAR()’. Expected Date, received DateTime” Can you point me in the right direction to resolve this?
LikeLike
Hi Ted – looks like you are using a date/time field, so you will need to wrap each instance of that API name in a DATEVALUE() function to extract the date portion. Line 4 should look like this:
YEAR(DATEVALUE(DateTime__c)) – YEAR(TODAY())
LikeLike
Thanks again. That seems to have worked.
LikeLike
Hi Evan, I want to thank you for this, it is really helpful! I was wondering if there’s any way to show the comparison between the quarters of the current year and the quarters of the previous f year. For example, i want to compare the Q3 of the current fiscal year vs the Q3 of the previous. Thank you so much.
LikeLike
Hi Santiago – as long as you think in relative terms, this is possible. Right now in December, Q3 would be “last quarter”. The timeframe you want to compare to is “5 quarters ago”. Using this formula field as the source of a dashboard filter, you would set it to:
Contains -1Q, -5Q
– display text: Previous Quarter YoY
You could set up a few variations of this so people could run an analysis of “Q3” at any point in the year (as well as any other quarter):
Contains -2Q, -6Q
– display text: 2 Quarters Ago YoY
Contains -3Q, -7Q
– display text: 3 Quarters Ago YoY
Contains -4Q, -8Q
– display text: 4 Quarters Ago YoY
LikeLike
Yes, I figured it out after I posted; I fixed my formula as well to show the pipe:) This helps to keep it consistent. Thanks again for your help.
LikeLike
Thank you, Evan!! This is great solution to the various ask for YTD/QTD/MTD etc. I have a question though; I see no data shows up for Current QTR and current QTD; rest I am getting results. Even though I have orders/closed won opportunities to report for current QTR; it should show up in the dashboard. Any insight would be helpful! We use standard CY for our reporting.
LikeLike
Hi Devki. In your source report, add the new “relative time frame” formula field as a column and then filter the report down to dates in the current quarter. What value are you seeing in the “relative time frame” formula field? There should be a portion that shows “0Q” or “0QTD”. If you are seeing this, then the formula is correct. Next, check the dashboard filter to make sure you are setting the appropriate value and you are using the “contains” operator. Let me know if you still have trouble.
LikeLike
Thanks so much, Evan! You are a savior:) . My formula is good but I just removed pipe from the filter and it is working like charm! I had it |0Q, |0QTD but now with 0Q, 0QTD it is showing results. I also added it as column in the report; it is showing up as |0CYTD|0FYTD0QTD|0MTD|-2WTD|-15D! Let me know if I am on right track. Thanks again for your help.
LikeLike
Hi Devki – thanks for pointing this out. I had left out a pipe from the formula in the post accidentally. I’ve added this in so you’ll be able to distinguish 10 quarters (10Q) from 0 quarters (0Q) by including the leading pipe in your filter (“|10Q” and “|0Q”)
LikeLike
Excellent solution Evan! I managed to work this out for the most part, however I’m stuck as to why my dashboard showing monthly sales is only displaying up to November across all filters. Any assistance would be greatly appreciated.
LikeLike
Hi Ben. Do your source reports have any time frame filters applied? Do you have data for time periods beyond November that meet the rest of the filter criteria you applied?
LikeLike
Thanks for all of the work and the excellent solution. I have a use case almost identical to Tracy Dye above. I think I have it mostly worked out but am assuming I have made a mistake or left something out. When I select a filter for total members joined per year, day week, etc, I am only getting the previous year to show. Any ideas on what I may have done wrong?
LikeLike
Hi Spencer – double check the filters in your source reports. You will want to open those up to view “all time” and then save the report. Then for the dashboard filter set up on the formula field you created from the instructions in this post, make sure to set values you want to filter down to. Here are some basic options for showing comparisons:
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
When you apply a filter to the dashboard, it will narrow down the data from all of your source reports and each dashboard component will refresh. The one grouped by day will show a bar for each day in the timeframe you selected, the one grouped by month will show a bar for each month in the timeframe you selected, etc
Let me know if you still have trouble
LikeLike
Thanks Evan! I realized that after the fact 🙂
Just another quick question: after adding this to the objects, I can still only filter for the field on one object, right?
LikeLike
After you create the fields on the objects and add the dashboard filter, you can specify the “equivalent fields” on each dashboard component by clicking on the filter icon in the top-right corner of that component. This would allow you to use a different “Relative Time Period” field for each dashboard component. I use this trick when I’m building a dashboard that analyzes data from multiple sources for a specified timeframe – something like showing a component for Opportunities based on Close Date next to a component for Cases based on Created Date.
LikeLike
I love the idea of this, but have some troubles implementing. I was able to add the formula to all my reports, however when I want to add the filter to the dashboard (that I used the reports on), I am not able to use this. It only shows me object fields, not the added reporting field.
Would love some help with this, thanks!
LikeLike
Hi Jasmina – It sounds like you created a row-level formula in a report with the formula above. I don’t believe these are available for dashboard filters (the documentation doesn’t state row-level formula compatibility in dashboard filters – https://help.salesforce.com/articleView?id=reports_formulas_row_level_limits.htm&type=0).
Nevertheless, you should be able to create a custom field on the object from the setup menu, then choose the “Formula” option, and follow the rest of the steps in this post. Once the field is created, it will be available in standard report types. If you are using a custom report type, be sure to add the new field to the custom report type layout.
Let me know if you still have trouble getting this to work.
LikeLike
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.
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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?
LikeLike
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.
LikeLike
This is great! May I please ask how would you filter on QTD for the past 5 quarters?
LikeLike
Hi Crystal – you can use this for the dashboard filter criteria:
Contains -5QTD,-4QTD,-3QTD,-2QTD,-1QTD
– display text: Last 5 QTD
If you want to see the current QTD, add in |0QTD
LikeLike
Thank you!
LikeLike
This is seriously profoundly helpful. There is no way to do this on the Forecast Quota or Forecasting Items objects though sadly. Or do you know of something else there?
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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"
&
"|"
LikeLiked by 1 person