Performing Year-to-Date (YTD) and Month-to-Date (MTD) Analysis

Published:

This article explains how to perform Year-to-Date (YTD) and Month-to-Date (MTD) analysis using the Line Chart Widget in Bold BI®.

Use Case

In this use case, we demonstrate how to display sales data for both Year-to-Date (YTD) and Month-to-Date (MTD) periods within a single widget. This allows users to switch easily between these time frames, offering a comprehensive view of sales trends. Follow the step-by-step instructions below to set up and display YTD and MTD data on your dashboard.

Step 1: Create a Dashboard Parameter

Refer this document to learn how to manually add values to a dashboard parameter.
Add the following values to the parameter:
- Year-to-Date (YTD)
- Month-to-Date (MTD)
Bind the created parameter to the Combo Box Widget for dynamically filtering data based on the user’s selection.

Step 2: Create Expressions for Sales Details and Date Columns

Year-to-Date (YTD) Expressions

Sales_Details_On_YeartoDate:
It calculates the total sales from the start of the current year to today’s date.

SUM(CASE 
           WHEN [CreatedDate] >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) 
                AND [CreatedDate] <= TODAY()
           THEN [SalesDetails]
           ELSE 0 
   END)

YeartoDate:
Filters and returns the CreatedDate for records between January 1st of the current year and today’s date.

   CASE 
       WHEN [CreatedDate] >= DATEADD(YEAR, DATEDIFF(YEAR, 0, TODAY()), 0) 
            AND [CreatedDate] <= TODAY()
       THEN [CreatedDate]
   END

Month-to-Date (MTD) Expressions

Sales_Details_On_MonthtoDate:
It calculates the total sales from the 1st day of the current month to today’s date.

SUM(CASE 
       WHEN [CreatedDate] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
            AND [CreatedDate] <= TODAY()
       THEN [SalesDetails]
       ELSE 0 
   END)

MonthtoDate:
Filters and returns the CreatedDate for records between the 1st day of the current month and today’s date.

 CASE 
       WHEN [CreatedDate] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
            AND [CreatedDate] <= TODAY()
       THEN [CreatedDate]
 END

Values Expression

Dynamically selects either the YTD or MTD expression based on the parameter value selected in the Combo Box.

CASE
   WHEN @{{:Parameter1}}='YeartoDate' then [Sales_Details_On_YeartoDate]
   WHEN @{{:Parameter1}}='MonthtoDate' then [Sales_Details_On_MonthtoDate]
END

Column Expression (Time Series Analysis)

Dynamically formats and displays the date on the X-Axis based on the selected parameter (YTD or MTD).

CASE
  WHEN @{{:Parameter1}} = 'YeartoDate' THEN FORMAT(COALESCE([YeartoDate], GETDATE()), 'MMM yyyy')
  WHEN @{{:Parameter1}} = 'MonthtoDate' THEN FORMAT(COALESCE([MonthtoDate], GETDATE()), 'dd-MM-yyyy')
END

Step 3: Bind the Created Expressions to the Chart

Drag and drop a Chart Widget onto the dashboard.
Configure the chart as follows:
- Use the Values Expression for the Y-Axis.
- Use the Columns Expression (Time Series Analysis) for the X-Axis.

Step 4: Finalize and Preview

Combine the Combo Box and Line Chart widgets using the Combined Widget feature.
Use the Combo Box to toggle between viewing data for the Year-to-Date or Month-to-Date.
Preview the dashboard to ensure:
Year-to-Date (MTD):

Month-to-Date (MTD):

By following these steps, users will be able to display sales data for both the current year and the current month, offering an interactive and dynamic view of sales trends.