How to Create a Monthly and Yearly Line Chart in Power BI

In advanced Power BI reporting, it is frequently necessary to visualize key performance indicators (KPIs) aggregated specifically by month and year. While Power BI automatically handles date hierarchies, sometimes a cleaner, continuous axis is required for clear trend analysis. This specific process involves generating a composite date field to ensure accurate sorting and display, a common requirement when working with continuous time-series data across multiple years.

The following detailed, step-by-step example illustrates precisely how to construct a Line Chart that accurately displays summarized values categorized by both month and year. This method guarantees that your months are ordered chronologically, regardless of year boundaries, which is crucial for identifying long-term trends:

We will now dive deep into the necessary data modeling and DAX calculations required to achieve this professional result, focusing on best practices for date handling and visualization setup in the Power BI Desktop environment.

Step 1: Loading the Source Dataset into Power BI

The foundation of any meaningful data visualization project in Power BI is the quality and structure of the imported data. Before creating the chart, we must first load the relevant dataset into the Power BI Desktop environment. This dataset should contain, at minimum, a standard date column and a measure column (such as Sales, Revenue, or Quantity) that we intend to aggregate and visualize over time. For optimal results, ensure your data is clean and prepared prior to loading.

For this specific tutorial, we will utilize a sample dataset that tracks daily sales figures for a company across multiple years. It is crucial that the ‘Date’ column is recognized by Power BI as a Date type; otherwise, the required time-intelligence functions and hierarchy creation will not function correctly. If your date column is imported as a text field, you must convert its data type using the Power Query Editor before proceeding to the next steps of data manipulation and DAX calculation.

Below is a representation of the initial dataset we will be working with. Notice the distinct ‘Date’ column and the ‘Sales’ column, which will serve as our primary measure for the Y-axis aggregation. This raw data structure is typical for time-series data analysis:

Step 2: Generating a Dedicated Month and Year Column (The Crucial Step)

The standard date hierarchy in Power BI often breaks dates down into Year, Quarter, Month, and Day. While useful for drill-down functionality, plotting this default hierarchy on a Line Chart can sometimes lead to jagged, segmented views if not properly managed. To create a smooth, continuous axis where the chronological order of months spanning multiple years is maintained (e.g., Dec 2022 followed immediately by Jan 2023), we must construct a new calculated column that explicitly combines the month and year into a single chronological string.

To initiate this data modeling change, navigate to the Table tools tab found along the top ribbon in the Power BI Desktop interface. This step is executed within the Data View rather than the Report View. Within this section, locate and click the New column icon. This action opens the formula bar, allowing us to input the necessary DAX expression to define the new column’s values, which will combine the necessary date components into a single label.

Step 3: Understanding the DAX Formula for Date Formatting

We will now input a powerful DAX formula utilizing the FORMAT function. The purpose of this function is to take our existing date field and convert it into a standardized text string that clearly represents the Month and Year, ensuring consistency across all records. This newly created column will serve as the backbone of our chart’s X-axis, providing the necessary labels.

Type the following precise formula into the newly activated formula bar. Note that 'my_data' refers to the name of your data table, which may need adjustment based on your actual data model. The output is a formatted text string that is easily readable and chronologically meaningful:

month_year = FORMAT('my_data'[Date], "MM/YYYY")

The format string "MM/YYYY" instructs DAX to extract the two-digit month and the four-digit year, separated by a forward slash. Upon execution, this formula creates the new column named month_year that only contains the month and the year of each date, thereby simplifying the subsequent visualization steps. Crucially, if you intend to sort this text column later, you must return to the Data View and set its ‘Sort by Column’ property to the original Date column to maintain correct chronological order.

After successfully applying the DAX calculation, your data table will be updated to include this new, essential column. Observe how the month_year column neatly groups the dates, providing the clean X-axis labels we require for plotting accurate time-series data:

Step 4: Initiating the Line Chart Visualization

With our data model now prepared with the necessary chronological identifier, the next step is to move from the data modeling view back to the primary reporting canvas. This is where we will select and insert the Line Chart visual that will display our sales trends. Begin by clicking the Report View icon, which is typically situated on the left-hand side navigation pane of the Power BI Desktop application, ensuring you are ready to configure the visual elements.

Once you are on the report canvas, direct your attention to the right side of the screen where the Visualizations pane is located. This pane hosts all available chart types, organized for easy selection. Locate and click on the Line chart icon, which is optimized for displaying trends over time. A basic, empty container for the visual will immediately appear on your report canvas, ready for field configuration.

Selecting the correct visual type is paramount for visualizing time-series data, and the Line Chart is the industry standard for tracking continuity and change over a chronological axis. Ensure the visual is selected before proceeding to drag fields into the respective wells.

The insertion of the visual results in a blank placeholder element on the report canvas, as shown below. This placeholder is now awaiting the necessary data inputs to populate its axes and values, transforming it into a functional visualization:

Step 5: Configuring the Visualization Fields (X-axis and Y-axis Setup)

The core configuration involves mapping the relevant columns from our data table to the corresponding fields within the Line Chart visual. For a proper month-and-year display, we must utilize both the calculated month_year column and the original Date column, as this is critical for correct sorting and hierarchy setup, a complexity inherent in plotting text-based date labels chronologically.

Follow this precise sequence for placing the fields into the Visualizations pane:

1. Drag the custom calculated column, month_year, and place it under the X-axis label. This provides the correct display name for the chronological points, pulling the formatted text we created in Step 2.
2. Next, drag the original Date variable and place it immediately underneath the month_year column, still within the X-axis field well. This action sets up the crucial date hierarchy required for accurate sorting of the month/year text field and provides the underlying structure for refinement.
3. Finally, drag the measure variable, Sales, and place it under the Y-axis label. This measure determines the height of the line chart points, representing the aggregated sales totals for each corresponding month/year period.

The Power BI interface should now reflect this specific arrangement within the fields pane, ensuring that both the display format and the underlying sort logic are present before we proceed to refinement:

Step 6: Fine-Tuning the Axis Hierarchy for Accurate Time Series Display

The dual configuration from the previous step is a prerequisite for correct sorting, but it introduces unnecessary hierarchy elements that clutter the final Line Chart. Since we only want to display the Sales aggregated by the specific Month and Year combination, we must remove the redundant components automatically generated by Power BI when a date field is added to the axis.

The following cleanup steps are essential to achieve the desired visual output, transforming the chart from a standard drill-down hierarchy into a continuous time-series plot:

• First, locate the month_year variable within the X-axis field well and remove it entirely. Its initial purpose was only to establish the display format, but the Date field’s hierarchy will manage the final components once the redundant levels are eliminated.
• Next, look at the remaining Date dropdown menu under the X-axis. This hierarchy automatically contains Year, Quarter, Month, and Day. Click on the dropdown arrow for the Date field and explicitly remove the Quarter and Day options, leaving only Year and Month.

By keeping only the Year and Month components of the original Date hierarchy, combined with the underlying custom column sorting (which, ideally, was set in the Data view for the month_year column to sort by the original Date column), we force Power BI to plot the sales data continuously along the chronological axis without interruption or unnecessary drill-down levels. This results in the clear, continuous trend line sought by analysts.

After successfully removing the unwanted elements, your X-axis field configuration should look simplified, containing only the necessary Year and Month elements:

The culmination of these precise data modeling and visualization steps results in a clean, highly readable Line Chart. This chart accurately displays the sum of sales aggregated by the month and year combination, clearly illustrating the long-term trends present in your sales data set, ready for executive review:

Conclusion: Analyzing Trends and Next Steps

Creating a highly specialized Line Chart aggregated specifically by month and year in Power BI requires a brief but essential detour into data transformation using DAX. By generating a composite date column and then carefully managing the axis hierarchy, we overcome the common limitations of standard date aggregation, resulting in a superior visual representation of time-series data. This precise method guarantees that the chronological sequence of data points is respected, regardless of year boundaries.

This technique is indispensable for financial analysts, business intelligence specialists, and data scientists who require accurate trend identification over multi-year periods without unwanted segmentation. Once the foundational chart is built, you are encouraged to further enhance its readability by applying formatting options, such as adjusting the title to clearly state the aggregation level, adding data labels for precision, and utilizing conditional formatting to highlight critical peaks or troughs in the sales performance.

The following resources offer additional tutorials explaining how to perform other common and complex tasks within the Power BI environment, helping you unlock the full potential of your business intelligence reporting:

• How to Calculate Running Totals in Power BI
• A Comprehensive Guide to Using Calculated Columns vs. Measures
• Implementing Advanced Filtering Techniques in Power BI Reports