Some years ago, I wrote a blog post about how to create a chart that compared monthly sales, to the same month in the previous year. The approach required quite a bit of XML editing and the chart was limited to only comparing this and last year. No data from two or three years ago.
Today, with the Unified Interface, that approach does not work any longer due to limitations in how much XML editing we can apply to the charts. So, on the cusp of everyone moving to the Unified Interface, it is time to have a look at how this year over year comparison can be done today.
First, this year over year comparison is very easy to do in the Excel and Power BI. Those services are not necessarily always available in this context, so let’s investigate how we can use the built-in charts..
Rather than relying on a massive amount of chart customizations, I’ll use the new Power Platform Dataflows feature to create an entity that calculates the Actual Revenue per month and year. Then I will create a chart for that new aggregated entity instead.
Normally, Power Platform Dataflows are used to import external data to CDS, but in this case I’ll use it to create an aggregated table with only the exact values I need for the chart. Then do some chart XML updates to present the chart better. Finally, set up a recurrence on the Dataflow to ensure data is up to date.
Official documentation for the Dataflows are available here https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows
I recorded all the steps on a video which ended up being much longer than anticipated, so I added a table of contents to it with links that jump to each segment. That is only available when viewing the video directly on YouTube, so you can jump to a specific point or skip sections you are already familiar with.
- Intro and demonstration of outcome
- Create Power Platform Dataflow
- Use Power BI Desktop client to create the Power Query code
- Copy the Power Query code to the Dataflow
- Use Dataflow to create the new entity used for the chart
- Create new dashboard and views for the new entity
- Create the chart
- Use Advanced Chart Editor in XrmToolBox to update orders, value formatting and colors
- Set up a recurring refresh for the Dataflow and modify the query to only include recent updates
- Test update cadence by updating an opportunity as won.
Towards the end I set the refresh cadence of the Dataflow to one minute. This is a very aggressive approach and in a Production environment should probably be around every 10-30 minutes or so. What refresh cadence to set it to exactly will largely depend on the amount of records processed.
When using Power Platform Dataflows, I’d recommend turning off user access auditing, as well as auditing on the table that you are importing to. Otherwise you might get some very large audit tables very quickly.
Thanks for reading. Hope you saw something useful. I definitely plan to start exploring the Unified Interface more and how we can also use the new Dataflows in conjunction with our setup, so be sure to follow me on Twitter Follow @CRMChartGuy or sign up for the newsletter.
crm chart guy 
Very interesting video and use of dataflows 🙂
I was looking at dataflows myself, trying to get in some data on an existing entity, but I’m not seeing how I can map fields to keys on the entity. Then, when running the dataflow it tries to create new records rather than updating existing records. Is that something you’ve encountered?
Hi Ulrik,
Having created the chart successfully, is it possible to add it to the Account form? What need to be done so that the chart can be added by inserting a Sub-Grid on the Account form? Thanks!