Table of Contents
To create a line chart in Power BI that displays data by month and year, you will need to follow these steps:
1. Start by selecting the “Line Chart” visualization option from the “Visualizations” pane in Power BI.
2. In the “Fields” pane, choose the data fields that you want to include in your chart, such as the measure (e.g. sales) and the date field.
3. Next, drag the date field to the “Axis” section of the “Visualizations” pane.
4. In the “Format” section of the “Visualizations” pane, under “X-Axis”, click on the drop-down menu next to “Type” and select “Continuous”.
5. Under “Time Granularity”, click on the drop-down menu and select “Month”.
6. To display the data by year, drag the date field to the “Legend” section in the “Visualizations” pane.
7. You can also customize your chart by adjusting the colors, labels, and other formatting options in the “Format” section of the “Visualizations” pane.
8. Your line chart should now display the data by month and year. You can further refine it by adding filters or other visualizations to your Power BI report.
By following these steps, you can easily create a line chart in Power BI that presents your data in a clear and organized manner by month and year. This can help you visually analyze trends and patterns over time, making it a useful tool for data analysis and decision-making.
Power BI: Create a Line Chart by Month and Year
Often you may want to create a line chart that displays the values of some variable summarized by month and year.
The following step-by-step example shows how to create the following line chart by month and year in Power BI:
Let’s jump in!
Step 1: Load the Dataset
First, we will load the following dataset into Power BI that contains information about the total sales made on various dates by some company:
Step 2: Create Column that Contains Month & Year
Next, we will add a new column to the table that contains the month and year of each date.
To do so, click the Table tools tab along the top ribbon, then click the New column icon:
Then type the following formula into the formula bar:
month_year = FORMAT('my_data'[Date], "MM/YYYY")
This will create a new column named month_year that only contains the month and the year of each date:
Step 3: Insert the Line Chart
Next, click the Report View icon on the left hand side of the screen:
Then, on the right side of the screen under Visualizations, click the Line chart icon:
An empty line chart will be inserted:
Step 4: Populate the Chart
Next, drag the month_year variable under the X-axis label, then drag the Date variable under the X-axis label, then drag the Sales variable under the Y-axis label:
Next, delete the month_year variable under the X-axis label, then delete the Quarter and Day options under the Date dropdown menu:
The end result will be a line chart that displays the sum of sales by month and year:
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: