These custom checkbox fields can be used to determine if an Appointment is taking place during the current Month/Year and is on or before the current day of the year. This allows you to Report on MTD and YTD metrics compared to the previous year. "How are we doing This Month compared to This Month, Last Year?
In this example we are using the Appointment object and the Appointment's Start date, However, you might also want to add these checkbox formulas to the Sale object and use the Sold On date in the formula. Allowing you to report on Sales volume for This Month compared to This Month, Last Year.
Create two (2) Formula Checkbox Fields on the Object
MTD:
DAY( i360__Start__c ) <= DAY(TODAY())
YTD:
( i360__Start__c - DATE(YEAR( i360__Start__c ), 1, 1) + 1)
<=
(TODAY() - DATE(YEAR( TODAY() ), 1, 1) + 1)
___________________________________________________________________
Note: You will replace the Start date field with the Sold On field if creating these on the Sale object.
Here is how to breakdown the YTD formula:
( i360__Start__c - DATE(YEAR( i360__Start__c ), 1, 1) + 1)
<=
(TODAY() - DATE(YEAR( TODAY() ), 1, 1) + 1)
Subtracting dates from each other = returns a Number value
DATE(YEAR(DateField))
• Pulls out the year value of the DateField > which is now a number return value > DATE() turns it BACK into a year Date.
DATE(YEAR(DateField)), 1, 1)
• Formats the pulled out year into a Date value for the first day of that year
• (YYYY, MM, DD) = (2024, 1, 1)
( i360__Start__c - DATE(YEAR( i360__Start__c ), 1, 1) + 1)
• Returns number of days between the Appointment Start Date and the beginning of the Year of the Start Date, then adds 1 (assuming this is there to add back the ‘1’ of the Start Date, so you get a true count of the days since the beginning of the year, including the Day of the Start Date
(TODAY() - DATE(YEAR( TODAY() ), 1, 1) + 1)
• Same as above, but for the current day
Ex:
Start Date 2/11/2022
Today Date 1/11/2024
(2022, 2, 11) - (2022, 1, 1) + 1
<=
(2024, 1, 11) - (2024, 1, 1) + 1
OR
41 + 1 = 42
<=
10 + 1 = 11
Create Report grouped by Date
To see MTD data in a Year over Year format, Filter for the YTD checkbox equals True AND the MTD checkbox equals True.
Your Date Range can be the Current and Previous Calendar Year.
Be sure to Group the Report on the Appointment Date field (or Sold On date), by Calendar Month.
This will display MTD metrics by Calendar Year.
Note: You CAN have sub-groupings. This example report is also being grouped by the Sales Rep 1 field.
To see MTD metrics Year over Year going back to the beginning of your system's data, click Customize.
Change the Date Range to All Time
You might also choose to add a chart to the Report to display the data in a graph.
Create a Dashboard instead
To use the report data in a dashboard, you may want to split it into separate reports.
In this example, we've split this report into three date ranges: Current CY, Previous CY and 2 CY Ago (two calendar years ago). Save a separate Report using each Date Range. Filter for the YTD checkbox equals True AND the MTD checkbox equals True.
When creating the Dashboard we will use each Report as a Data Source for each dashboard component.
In the example below, we have 6 charts being fueled by six unique Reports.
The Charts within the Dashboard display the data queried from each of these Reports.
The top row shows Year to Date metrics compared to the same YTD timeframe for the previous year and two years ago. To accomplish this you will Filter for the YTD checkbox equals True. The MTD filter is not needed for this row of Reports.
The second row shows MTD metrics for the current month/year and compares to the same MTD timeframe for the previous year and two years ago. Filter for the YTD checkbox equals True AND the MTD checkbox equals True.
IE: How many Appointments did we set in February of last year compared to this year?
Note: This Dashboard was run in January, making the MTD and YTD data the same. When this dashboard is refreshed in February the data will be different.