Skip to content
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Group Revenue by Month

Group closed won revenue by owner and by month with the date grouping of the Pivot Table, month names in full, so a sales director sees who closed what when.

A sales director reviews closed won revenue once a month: who brought it in, and in which month. Grouping Close Date by month gives one column per month, with the owners down the side and the yearly total of each rep on the right.

Pivot table of closed won revenue with one row per owner and one column per month written in full, with yearly totals per owner and monthly totals
Revenue each owner closed, month by month, with full month names.

Before you start

  • A Pivot Table on Opportunity. Summarize Pipeline by Stage and Quarter builds one; you can reuse it or start a new component the same way.

  • Opportunities in the Closed Won stage this year, with an Amount.

Steps

1

Keep only this year's won deals

  1. Select the Pivot Table and open its query.

  2. Set the filter conditions to Stage equals Closed Won and Close Date equals THIS_YEAR.

  3. Save the query.

2

Put the owners in the rows

  1. In Data Mappings, set the only item of Group Rows to the opportunity owner's name (Owner.Name).

  2. Turn on Custom Label and set Label to Owner.

3

Group the close dates by month

  1. Set the only item of Group Columns to Close Date.

  2. Group: Month.

The Group list decides how dates are bucketed:

Group
Use it for

Year

Several years side by side

Quarter

One year, by quarter

Month

One year, by month

Week

One year, by week

Day

A short range, such as one month

Year-Quarter

A range longer than a year, by quarter

Year-Month

A rolling twelve months

Note: Month alone puts January of every year in the same column. The query is limited to this year, so that is what you want here. For a range that crosses a new year, pick Year-Month.

4

Spell the months out

Turn on Display Full Month Label on the same item. The headers read "January", "February" instead of an abbreviation.

The setting appears only when Group is Month or Year-Month.

Group Columns item for Close Date with Group set to Month and Display Full Month Label turned on
Display Full Month Label appears for Month and Year-Month.
5

Measure the revenue

  1. Keep one item in Aggregations: Field Amount, Measure Sum.

  2. Turn on Custom Label and set Label to Revenue.

  3. Remove any other measure, such as a deal count, if you reused the pipeline table.

6

Add the totals and a title

  1. In Content, turn on Show Grand Total. The last column shows each owner's revenue for the year, the last row each month's revenue for the team.

  2. In Header, set Title to Closed Won Revenue by Month.

7

Save and test

Click Save and open the page where the component is placed.

The table lists one row per owner and one column per month with closed won deals, from "January" onward. Each cell holds the revenue that owner closed that month. A month in which an owner closed nothing shows $0.00.

Troubleshooting

Months of two years are added together

Group is Month and the query covers more than one year. Either keep the query to THIS_YEAR, or switch Group to Year-Month to get one column per month and year.

Display Full Month Label does not appear

Group must be Month or Year-Month. With Quarter, Week or Day there is no month name to write out.

"Group By with ROLLUP or CUBE must contain 3 fields or less"

The table has more than three groups in Group Rows and Group Columns together, and a date grouped by Year-Quarter or Year-Month counts as two. Owner plus Year-Month already uses three: to add a third group, switch the date to Month.

Was this helpful?