Comparison of Year-over-Year (YOY) and Month-over-Month (MOM) Sales analysis

Published:

This article outlines the steps to achieve Year-over-Year (YoY) and Month-over-Month (MoM) sales analysis by leveraging separate data sources in code view mode. Follow the detailed steps below to set up and visualize the sales comparison effectively

Steps to Create Month-over-Month (MOM) Sales Analysis

Create a Data Source
Refer to this help document to successfully create a new data source for by connecting to your database.
Switch to Code View
By default, the data design view opens in design mode. To switch to code view, enable the slider option in the tools pane, as shown in the following image:

Add the Queries and Save the Data source
Copy and paste the provided SQL queries into the respective data source configuration.

Query for Month-Over-Month (MoM) Sales Analysis:

SELECT
   FORMAT([SALES].[CreatedDate], 'MMM yyyy') AS "Month Year",
   SUM([SALES].[SalesDetails]) AS "Current Month Sales",
   LAG(SUM([SALES].[SalesDetails])) OVER (ORDER BY YEAR([SALES].[CreatedDate]), MONTH([SALES].[CreatedDate])) AS "Previous Month Sales",
   SUM([SALES].[SalesDetails]) - LAG(SUM([SALES].[SalesDetails])) OVER (ORDER BY YEAR([SALES].[CreatedDate]), MONTH([SALES].[CreatedDate])) AS "MoM Change"
FROM
   [dbo].[SALES] AS [SALES]
GROUP BY
   YEAR([SALES].[CreatedDate]),
   MONTH([SALES].[CreatedDate]),
   FORMAT([SALES].[CreatedDate], 'MMM yyyy')

Description:
This query calculates monthly sales trends by summarizing sales for each month, comparing the current month’s sales to the previous month’s sales, and determining the month-over-month (MoM) change. It groups data by year and month, displaying the date in “Month Year” format.

Bind the columns to the chart
1. Drag and drop a Chart Widget onto the dashboard.
2. Configure the chart as follows:
- Use the MoM Change column for the Y-Axis(Value(s) section).
- Use the Month Year Column for the X-Axis(Column(s) section).
Add a Tab Widget to the Dashboard
- Use the Tab Widget inside the Combined Widget for a clear and organized visualization.
- In the First Tab, display Month-over-Month Analysis chart.
Month-over-Month Sales Analysis Visualization:

Steps to Create Year-over-Year (YOY) Sales Analysis

Create a Data Source and Switch to Code View
Follow the step1 and step 2 outlined in the Month-over-Month (MOM) Sales Analysis section for creating the data-source and switching to code view.
Add the Queries and Save the Data source
Copy and paste the provided SQL queries into the respective data source configuration.

Query for Year-Over-Year (YoY) Sales Analysis:
```
SELECT
   YEAR([SALES].[CreatedDate]) AS "Year",
   LAG(SUM([SALES].[SalesDetails])) OVER (ORDER BY YEAR([SALES].[CreatedDate])) AS "Previous Year Sales",
   SUM([SALES].[SalesDetails]) - LAG(SUM([SALES].[SalesDetails])) OVER (ORDER BY YEAR([SALES].[CreatedDate])) AS "YoY Change"
FROM
   [dbo].[SALES] AS [SALES]
GROUP BY
   YEAR([SALES].[CreatedDate]) 
```
Description:
This query calculates yearly sales trends by summarizing total sales for each year, comparing the current year’s sales to the previous year’s sales, and determining the year-over-year (YoY) change. It groups data by year and orders it chronologically for comparison.
Bind the columns to the chart
1. Drag and drop a Chart Widget onto the dashboard.
2. Configure the chart as follows:
- Use the YoY Change column for the Y-Axis(Value(s) section).
- Use the Year Column for the X-Axis(Column(s) section).
Add a Tab Widget to the Dashboard
After creating the MoM Sales analysis in the first tab (by referring to step 5 mentioned in the Month-over-Month (MOM) Sales Analysis), click the “+” icon to add another tab for the Year-over-Year Analysis chart.

Year-over-Year Sales Analysis Visualization: