Compare This Year to Last Year – Unified Interface edition

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.

Year over year, month by month comparison. Power App, Dynamics 365 Unified Interface.
Chart comparing monthly revenue to previous years in Unified Interface

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.

Open in YouTube to skip to sections.
  • 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 or sign up for the newsletter.

One thought on “Compare This Year to Last Year – Unified Interface edition

  1. 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?

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s