Compare This Year to Last Year with a Dynamics CRM chart

Comparing sales performance this period vs. the same period last year is a fairly standard metric. However, to create a chart that compares the period this year, with last years in the same date range can be tricky. For example, today is June 21st. I want to see my actual sales for this year to date, compared to my actual sales last year, until June 21st. Or Year to Date vs. Last Year to Date (YTD vs. LYTD).

YTD vs LYTD MS Dynamics CRM Chart Year to Data vs Last Yeard to Date

Or maybe I want to compare my sales this year and last year by month.

Chart compare to last year by month overlapped MS Dynamics CRM chart after customizing xml

In this post I will detail how I created both of these charts.

Compare YTD to LYTD in MS Dynamics CRM

Before I start to create the chart, I need to get some components ready first.

These are:

  • View of Won Opportunities – This and Last Year
  • Fetchxml filter for YTD
  • Fetchxml filter for LYTD

Create the View for the chart

Open Advanced Find on opportunities and create the following view:

Advanced Find - View Won This AND Last Year

Name it “Won Opportunities: This And Last Year.” Always use this view with the charts in this post.

After saving it, keep Advanced Find open and use it to create the fetchxml filters we need to modify the chart xml with.

Create the following filters and download the fetchxml. You can download the fetchxml without saving the View first.

Filter for YTD

Advanced Find - This Year

 Filter for LYTD

Advanced Find - Last Year and Older Than 1 Year

Using the Older Than X Years will make sure we only get the LYTD date range for the won opportunities.

Note: Are you on a version earlier than CRM2015, you can use “Older Than X Months”, with X being 12, and get the same result.

Chart Editor

Time to open up the chart editor.

Save a chart similar to the settings here.

Chart Editor to create YTD vs LYTD in MS Dynamics CRM

The two series for actual revenue, is what will be modified to YTD and LYTD using the filters we just downloaded from Advanced Find.

Now the chart xml is ready to export and we can have some fun!

Fetchxml edits

The two attributes for the actual value series will need to be edited so they use the filters we just downloaded.

xml original attrbiutes

Here is the fetchcollection with the additions made and the filters downloaded from Advanced Find highlighted in yellow.

      <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" name="statecode" /> 			<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" /> 				  <filter> 					  <condition attribute="actualclosedate" operator="last-year" /> 					  <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> 				  </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" /> 				  <filter> 					   <condition attribute="actualclosedate" operator="this-year" /> 				  </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>

Fetchcollection edits:

  • Created link-entity outer joins so the series only includes the filtered data.
  • Added the YTD and LYTD filters downloaded from Advanced Find.
  • Renamed the aliases from _CRMAutoGen_aggregate_column_Num_0 or similar to more sensible names.
    • If renaming the aliases, remember to update them in the categorycollection too.

Other changes:

     </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </measurecollection>         </category>       </categorycollection>     </datadefinition>   </datadescription>   <presentationdescription>     <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">       <Series>         <Series XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </Series>       <ChartAreas>         <ChartArea BorderColor="White" BorderDashStyle="Solid">           <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">             <MajorGrid LineColor="239, 242, 246" />             <MajorTickMark LineColor="165, 172, 181" />             <LabelStyle Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>

  • Make sure the series are in the same order in both the categorycollection and in the series section
  • Custom LegendText
  • Custom LabelFormat
  • Format the Y axis numbers

The categorycollection is where you can adjust if YTD or LYTD should come first. In this example YTD comes first.

The new formatting is designed to show amounts in the thousands with negative and zero values suppressed. See this post for details on chart labels and formatting.

Import the XML and this should be your result.

YTD vs LYTD chart in MS Dynamics CRM. Custom chart xml.

Here is the chart and we can see how our performance is compared to the same period last year.

If you would like to see the data grouped by something else, such as customer or owner, you can open the editor and change the category.

Change GroupBy in editor for YTS vs LYTD chart, custom chart xml in MS Dynamics CRM

Just keep in mind that if you make any changes to the legend entries you will lose your xml customizations.

Compare to Last Year by Month

For better insights on our sales development, we can group the sales by month instead.

Start with the chart we just made and use the editor to change the groupby to Actual Close Date and Month.

Category Month

Click Save As and save the chart under a different name.

The result should look like this.

Compare to Last Year Month after changing groupby in chart editor.

Now we see the same data spread out per month instead. What I would like to do is include all of last years data and then have the month columns next to each other, so March of this year is next March of last year, etc.

In short, what I will do is remove the Older-Than-1-Year filter so we get all of last year. Next I will split the data onto two separate X axes. One for each year and set the chart to display them in sync with each other so the months line up.

Note: We are working with data by month. If data points are missing for a certain month, that month will not be included. For this chart, we are relying on having 12 data points for each year. If you do not have a data point for each  of the past months, then use dummy records to ensure a data point exists for the missing months. These can be of zero value and will not interfere with the calculations. You do not need records for future months.

Update filter and add secondary X axis

Export the chart xml.

First remove the “older than” clause in the fetchcollection.

              <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>

Now scroll down and add the secondary X axis. Both in the Series and Axis section.

<visualization>   <visualizationid></visualizationid>   <name>Compare to Last Year by Month - overlap axis</name>   <primaryentitytypecode>opportunity</primaryentitytypecode>   <datadescription>     <datadefinition>       <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" dategrouping="month" name="actualclosedate" />             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="this-year" />               </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </measurecollection>         </category>       </categorycollection>     </datadefinition>   </datadescription>   <presentationdescription>     <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">       <Series>         <Series XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </Series>       <ChartAreas>         <ChartArea BorderColor="White" BorderDashStyle="Solid">           <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">             <MajorGrid LineColor="239, 242, 246" />             <MajorTickMark LineColor="165, 172, 181" />             <LabelStyle Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>           <AxisX Minimum="0" Maximum="12.5" IntervalOffset="1" Interval="1" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">             <MajorTickMark LineColor="165, 172, 181" />             <MajorGrid LineColor="Transparent" />             <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisX>           <AxisX2 Minimum="12" Maximum="24.5" IntervalOffset="1" Interval="1" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">             <MajorTickMark LineColor="165, 172, 181" />             <MajorGrid LineColor="Transparent" />             <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisX2>		           </ChartArea>       </ChartAreas>       <Titles>         <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />       </Titles>       <Legends>         <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />       </Legends>     </Chart>   </presentationdescription>   <isdefault>false</isdefault> </visualization>

I added XAxisType=”Secondary” to the series for this year to indicate which series should go on the secondary X axis.

The AxisX2 section is identical to AxisX except for the “2” so you can copy it and add the number.

Import the chart xml back into MS Dynamics CRM.

Month over Month 2nd X axis added in chart xml

Notice that the secondary X axis has been added to the top of the chart. The blue columns are actually bound to the top (secondary axis) and the orange are on the bottom axis.

Overlap axes

Next step is to make the two axes overlap so it is easy to compare a month this year, to the same month last year.

We have 24 data points. One for each month of the two years. The series for last year is displayed on Axis 1 and should show points 0 – 12 (Jan-Dec of 2014). Axis 2 has the data for this year and should show points 12-24 (Jan-Dec of 2015).

To achieve that, I add Minimum and Maximum values to each axis so they are aligned.

Here are the chart xml axis edits.

<visualization>   <visualizationid></visualizationid>   <name>Compare to Last Year by Month - overlap axis</name>   <primaryentitytypecode>opportunity</primaryentitytypecode>   <datadescription>     <datadefinition>       <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" dategrouping="month" name="actualclosedate" />             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="this-year" />               </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </measurecollection>         </category>       </categorycollection>     </datadefinition>   </datadescription>   <presentationdescription>     <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">       <Series>         <Series XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </Series>       <ChartAreas>         <ChartArea BorderColor="White" BorderDashStyle="Solid">           <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">             <MajorGrid LineColor="239, 242, 246" />             <MajorTickMark LineColor="165, 172, 181" />             <LabelStyle Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>           <AxisX Minimum="0" Maximum="12.5" IntervalOffset="1" Interval="1" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">             <MajorTickMark LineColor="165, 172, 181" />             <MajorGrid LineColor="Transparent" />             <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisX>           <AxisX2 Minimum="12" Maximum="24.5" IntervalOffset="1" Interval="1" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">             <MajorTickMark LineColor="165, 172, 181" />             <MajorGrid LineColor="Transparent" />             <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisX2>		           </ChartArea>       </ChartAreas>       <Titles>         <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />       </Titles>       <Legends>         <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />       </Legends>     </Chart>   </presentationdescription>   <isdefault>false</isdefault> </visualization>

IntervalOffset=”1″ is added so the axis does not show the zero value (i.e. Dec 2014 on the 2015 axis).
Interval=”1″ is added to make the intervals consistent between the two axes and show every month.

Notice the maximums are set to 12.5 and 24.5. That is to make sure the data for the last month is displayed in full. If set to 12 and 24 only half the column would display.

Import the xml and this should be the final result.

Chart compare to last year by month overlapped. Chart xml custom MS Dynamics CRM

The chart type on Last Year can be changed to Line, ChartType=”Line”, to get this result.

Compare to Last Year Line chart. chart xml MS CRM

As always, hope you enjoyed the post and found something useful. Please follow me on Twitter or sign up for the newsletter to be alerted on new posts.

 

 

 

Advertisements

, , , , ,

  1. #1 by Marianne on June 21, 2015 - 1:46 pm

    Thank you for this great post. And for taking the time to work out great charts – and share your work.
    Already looking forward to adding this to my dashboard.

  2. #2 by Flynt on June 22, 2015 - 1:52 am

    Great job and thanks for sharing it!

  3. #3 by ukcrmguru on June 22, 2015 - 3:24 am

    Excellent post – charts are always better with some extra context, so showing this year’s results against last year’s is brilliant.
    You could of course substitute This Year / Last Year with This Fiscal Year / Last Fiscal Year for exactly the same sort of result for those working with a non-January year (April is a common starting point in the UK for example, to coincide with the start of the tax year). The “Older than X years” with X=1 remains the same in this model.
    You could of course also hide the X2 axis by using transparent colours for text, lines and tick marks to clean up the finished version. It ought to be possible to find a suitable “number” format to get the first X axis to show month names only. I need to do some testing to see if it responds properly to standard formats.

    Another approach would of course be to use Goals as the underlying entity, then use the same sort of techniques to show last year and this year by filtering based on the start date of the goal period. It might be easier to use an option set for the months in order to get nice labels on the X axis, and you would not need a secondary axis if you do this. Because Goals are pre-calculated the performance should be better – charting up to 24 values maximum rather then aggregating hundreds or thousands of Opportunity values on the fly.

    • #4 by CRM Chart Guy on June 23, 2015 - 4:47 am

      Thanks for the input Adam. For unknown reasons MS CRM does not support date formatting. Otherwise Format=”MMM” would have worked. That’s why I left both axes in there so you could see the year. It did work very briefly in one version of CRM2013, but suddenly disappeared again.

      • #5 by ukcrmguru on June 24, 2015 - 12:02 am

        Not surprised to find formatting does not work, especially on the axis since CRM does not do proper date axes, they are all treated as categories, so it will skip months with no data, as you point out.

  4. #6 by Christoph on June 23, 2015 - 11:20 pm

    Thank you for your post.

    How is it possible to compare the year-to-date performance for more than one year in the past? Lets say, “until 24.6.” of the last 5 years? Without problems this can be done for the last year, but how can I display this with out of the box filters in views or xml coding?

    Thank you.

  5. #7 by Marianne Christensen on July 2, 2015 - 2:02 am

    I just tried to copy these two charts. Thanks for pointing out the need for dummy datapoints to get the charts to show up correctly.
    I come up short on one point – how do you get rid of the secondary Y Axis? Deleting it in the XML does not seem to be the right option. It stays – and the color of the whole grid changes.
    If you could post the code (or mail it to me) so I don’t have to type off screenshots I would really appreciate it.

    • #8 by CRM Chart Guy on July 2, 2015 - 11:53 am

      Sounds like you just missed deleting the secondary Y axis reference in one of the series. YAxisType=”Secondary”

      • #9 by MezMarianne on July 3, 2015 - 2:17 am

        Ta daaaaa http://screencast.com/t/UoqkIxVnHB
        Secondary reference deleted – more dummy points for 2015 added and then I just “created” a really cool CRM chart. (or… you created it and I just copied your work.) You are welcome to swing by the office for coffee, cake, eternal gratitude and a CRM chat if you ever come to Denmark.

  6. #10 by Nicholas on July 14, 2015 - 10:08 pm

    I’m still amazed by your creativity. Thank you. I’ve used your coaching for maybe 20 charts and they look brilliant… AND are adding value in our org. One issue I am having is a combined Bar/Line chart with two axis. I need to scale both axis to the same max number. Any recommendation?

    • #11 by ukcrmguru on July 15, 2015 - 2:09 am

      @Nicholas if you have two axes but you want them to show exactly the same scale, why would you need two axes at all? Can’t you just plot everything on a single axis by getting rid of the YAxisType=”Secondary” (and optionally, getting rid of the YAxis2 definition completely).
      An alternative, if you feel you want two axes, perhaps to have explanatory labels on them, for example “Actual Sales” and “Targets”, is to plot both series on both axes, but make the second version of them transparent. This way both axes will have a max that is large enough to contain its own series and the opposing one.

      • #12 by Nicholas on July 15, 2015 - 11:58 am

        Man, I love this guy!!! Adam, you make this part of my life so easy. Thank you. Exactly what I wanted happen.

  7. #13 by Maaax on July 17, 2015 - 6:51 am

    Thank you ! You saved my day !
    This is just great 🙂

  8. #14 by Christoph on July 27, 2015 - 1:02 am

    Is it possible to have two different date fields on either axis? I don’t really know how to Change the Attribute Group by so that I can compare order entry Dates with plan Dates (two customized fields). Thanks a lot.

  9. #15 by Daryl Tohill on August 4, 2015 - 3:29 am

    Hi,

    I keep getting this error message whenever I try to import the xml file into crm.

    “The specified XML file “Compare To Last Year.xml” is either not valid XML or does not conform to the chart schema”.

    Would you be able to help me resolve this error or post the full source code so I can see where abouts I have gone wrong.

    Regards,
    Daryl

    • #16 by CRM Chart Guy on August 23, 2015 - 9:46 am

      I used this approach for both 2013 and 2015 environments, and I think also also 2011. So it should work. I’d recommend going through the steps from the beginning and do an upload between each edit to identify which component is causing the issues. That error could be something as simple as a missing < or " or a word property that is not capitalized correctly.

  10. #17 by Kathy on August 23, 2015 - 3:02 am

    Hi

    I was so excited to try this chart out for my companies CRM but like Daryl am also getting the error:

    “The specified XML file “Compare to Last Year Chart.xml” is either not valid XML or does not conform to the chart schema”

    Does the code work in Dynamics 2013? Xml is completely new to me, so maybe I got something wrong, but have checked and re-checked the code.

    Thanks
    Kathy

    • #18 by CRM Chart Guy on August 23, 2015 - 9:45 am

      I used this approach for both 2013 and 2015 environments, and I think also also 2011. So it should work. I’d recommend going through the steps from the beginning and do an upload between each edit to identify which component is causing the issues. That error could be something as simple as a missing < or " or a word property that is not capitalized correctly.

    • #19 by Scott on September 11, 2015 - 7:06 am

      I had received the same error in the beginning and I believe it might have been because I was copying and pasting my coding from one application to another (Notepad to Notepad ++). When I opened it up directly in Notepad++ to make my edits I did not receive the error when I tried to import it again.

  11. #20 by Evan Irla on August 24, 2015 - 10:34 am

    I attempted this with the incident entity and ended up getting two columns with the exact same data. What could I have done wrong?

    • #21 by CRM Chart Guy on August 24, 2015 - 6:51 pm

      You probably just need to replace your “count” aggregate with “countcolumn”.

      • #22 by Evan Irla on August 24, 2015 - 9:33 pm

        That was the issue! Thanks!

  12. #23 by CRmCDK on October 9, 2015 - 3:55 am

    This is amazing – and I have created charts with no major hickups. However – would it be possible to add a total column at the end the chart?

  13. #24 by Colin C on October 27, 2015 - 9:49 am

    Excellent stuff. I’ve only been working with CRM charts and XML for a matter of days but I was able to follow the steps above and replicate the chart shown against our active Orders across the whole business. Cheers!

  14. #25 by Tomas on November 15, 2015 - 10:29 am

    Thank you for the posts, really great. It expands the standard reporting possibilities via GUI a lot.

  15. #26 by Brian Garback (@iGarbo) on April 14, 2016 - 10:22 pm

    Incredible post. I’m so close, but weirdly I get the december 2015 bar repeating at the start of the chart. Could really use your help!

    {70A5006C-0FDD-E511-80E0-6C3BE5A8E644}
    Jobs ordered year over year
    new_job

    false

    • #27 by Brian Garback (@iGarbo) on April 15, 2016 - 12:15 pm

      I figured out that my filtering isn’t working. Weirdly both the This Year and Last Year series show data for all month from Jan 2015 – April 2016. Any chance you can help me find the issue?

  16. #28 by rexing14150 on May 25, 2016 - 2:24 pm

    Another brilliant solution. Two things I want as enhancements or polish.
    1. Both vertical axis must be scaled the same so the visual compares are acceptable. Right now, the difference in axis make the compares more favorable than in reality.
    2. I wrapped the X axis titles but would really like to suppress the year and only show the month.

    Thanks again for your thought leadership.

  17. #29 by Jawad Amellal on June 2, 2016 - 1:27 am

    Thanks for this post, I have a case that I need to display the records that have DateX greater than DateY.
    How can I do that?
    Thanks for your help

  18. #30 by Paul on June 17, 2016 - 8:47 am

    This is a great graph, thank you!

    I would like to do something similar where I show OPEN forecasted opportunities as well CLOSED opportunities (current month) in 1 graph. Do you know if Dynamics CRM can render a graph that displays the est. close date on the bottom x-axis and the act. close date as the top x-axis? At the same time, I want to show the est. revenue amount on 1 y-axis and the actual revenue on the other y-axis.

  19. #31 by Kenneth Van de Borne on October 15, 2016 - 3:11 am

    Does this only work with amount? I mean i want to compare the number of emails received and compare them this year/ last year, it doesn’t seem to work.
    It shows a value of 0 next to the month’s…

    • #32 by ukcrmguru on October 17, 2016 - 5:50 am

      If you are trying to do a count of something which is filtered, make sure to use the aggregate method “countcolumn”. usually, a regular count will give you all the records, instead of the filtered set. If you are seeing 0 instead, something else is wrong.

  20. #33 by Luke on December 1, 2016 - 2:09 pm

    I’m testing out this technique and have a count of contacts where createdon is in this year and last year.

    When using count, I get a total of 9872 on both bars, which is the correct total number of contacts for the whole period that I get when exporting the underlying view.

    When using countcolumn as advised in the article, I’m getting:

    – This Year: 707
    – Last Year: 7378

    This does not total 9872. Not even nearly. I’ve pivoted the data in Excel and the split should be:

    – Last Year: 1093
    – This Year: 8779

    Which is correct as it does in fact total 9872. Can you enlighten me on what is going wrong here?

    • #34 by CRM Chart Guy on December 1, 2016 - 3:10 pm

      Hi Luke, thanks for reading.
      First I’d normally check that the field you are using does in fact have a value for all the records. Otherwise countcolumn would exclude them, but with createdon that should not be an issue. May wanna give contactid a try though.
      Otherwise some of the dates could be outside the periods defined in the fetchxml. Have you tried running similar filters in Advanced Find to see what results you get there. If the numbers there don’t match what you get in the chart, then the issue is probably in the fetch section of the chart.

  21. #35 by Luke on December 4, 2016 - 1:52 pm

    Hi Again,

    Many thanks for coming back to me so quickly. I’ve resolved the issue after realising that I needed to link to a separate entity which contains my filter attributes.

    My company has recently migrated to Dynamics and there is no data for several months in 2015. You mention to add dummy records with 0 values but as my chart is based on counts, I’m not sure of the best way to achieve this. Also, I’m dubious about introducing redundant records to overcome CRM’s shortcomings. Is there any other way around this? Would the technique described in your earlier article ‘include-records-with-no-value-in-charts’ be an option here?

    Also, just to say, thank you for all your helpful insights. Which resources have you utilised to gain your excellent knowledge here? I’m struggling to find any decent documentation from Microsoft regards CRM charting and I get the impression that they’ve bolted this functionality on as a sales feature but not bothered to design it properly.

    I’m tasked with training my end users on Advanced Find and charts and to expect them to be able to edit XML to do things as basic as amending a legend or axes title is ludicrous. Most of them have no clue what XML even is! : (

    Thanks,

    Luke

  22. #36 by Luke on December 4, 2016 - 2:04 pm

    Sorry to keep bothering you but I also have a problem with the data bars overlapping each other for some months as well. I’m guessing this may be because I’ve had to play around with the Minimum and Maximum attribute values on both X Axes in order to get the months ticks to display properly. Any suggestions?

  23. #37 by Danus J on January 14, 2017 - 11:03 am

    Thank you. Everywhere I look for information on customizing charts in CRM 2016 I come across your site. At first I thought I would never be able to get familiar with customizing charts by editing its xml file. Your post has made is so easy to follow.

    Thank you much!!!!

  24. #38 by Devon on February 4, 2017 - 9:35 pm

    Thank you for your site. While trying to create a similar report when I attempt to place different filter criteria on each attribute it returns that there is no data to graph. When I remove the filter from one of either of the two attributes it will graph the correct results of the filter I left but it applies the filter to both series instead of just one. I tried changing the count to countcolumn and still received the same results. Any ideas on what could cause this?

    Sample code (second filter is same structure):

    Thank you

  25. #39 by Carsten on March 20, 2017 - 8:51 am

    would it be possible to add another series to the diagram so that i can create a stacked Chart e.g. for different product Groups to show their share off the total Revenue?

  26. #40 by Eunice Park on March 23, 2017 - 11:23 am

    Hi, I’ve created the new chart, but would like it to become the Default Chart on one of the views on my opportunity dashboard. Could someone please tell me how to do this?

    • #41 by Eunice Park on March 23, 2017 - 11:39 am

      Never mind I found it! 🙂

  1. Hosk’s Top CRM Articles of the week – 26th June – Hosk's Dynamic CRM Blog
  2. Hosk’s Top CRM Articles of the week – 26th June - Microsoft Dynamics CRM Community

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: