How to Sum by Year in Power BI tables?

“I have sales data spanning multiple years, and I need to analyze yearly trends, compare year-over-year growth, and calculate monthly running totals. How can I set this up in Power BI to create an effective yearly analysis dashboard?”

If you’re working with time-based data in Power BI, calculating yearly sums and comparisons is probably something you do quite often. Whether you’re analyzing sales, financial data, or performance metrics, Power BI offers multiple approaches to handle yearly calculations effectively.

In this example we will work with a sales table containing the following columns: Date (datetime), Product (text), Region (text), Sales (decimal), and Quantity (whole number). We’ll create a comprehensive solution using both Power Query and DAX approaches.

Setting Up Yearly Analysis in Power Query

1. Open Power Query Editor:
2. Click ‘Transform Data’ in Power BI Desktop
3. Select your date column and click the “Add Column” tab, then choose “Date” ? “Year” to extract the year from your date column.
4. Alternatively, you can click “Custom Column” named Year and use the formula

= Date.Year([Date]) 

3. Group by Year:

• Click ‘Group By’ in the Transform tab
• Choose ‘Year’ as the grouping column
• Add aggregations:
  • Sum of Sales
  • Sum of Quantity
  • Count of Transactions

Creating DAX Measures for Yearly Analysis

Follow these detailed steps to create and implement each DAX measure:

1. Basic Yearly Sum:

• Click ‘New Measure’ in the Home tab
• Enter the following formula:

   Total Sales by Year = 
   CALCULATE(
       SUM(Sales[Sales]),
       SUMMARIZE(Sales, Sales[Year])
   )

• Set the format to Currency
• Place the measure in your ‘Sales Measures’ display folder

2. Year-over-Year Growth:
   a. First, create a Previous Year Sales measure:

   Previous Year Sales = 
   CALCULATE(
       SUM(Sales[Sales]),
       SAMEPERIODLASTYEAR(Sales[Date])
   )

b. Then create the YoY Growth measure:

   YoY Growth = 
   VAR CurrentYearSales = CALCULATE(SUM(Sales[Sales]))
   VAR PreviousYearSales = CALCULATE(
       SUM(Sales[Sales]),
       SAMEPERIODLASTYEAR(Sales[Date])
   )
   RETURN
   DIVIDE(
       (CurrentYearSales - PreviousYearSales),
       PreviousYearSales,
       BLANK()
   )

• Format as Percentage
• Set decimal places to 2
• Add conditional formatting to show positive values in green and negative in red

3. Running Total by Month:
   a. First, ensure you have a proper date table:

• Go to Modeling tab
• Click ‘New Table’
• Create a date table using this DAX:

   Dates = 
   CALENDAR(
       MIN(Sales[Date]),
       MAX(Sales[Date])
   )

• Mark it as your date table b. Create the Running Total measure:

   Running Total = 
   CALCULATE(
       SUM(Sales[Sales]),
       DATESYTD(Sales[Date])
   )

• Set format to Currency
• Add to your measures folder

4. Average Monthly Sales:

   Avg Monthly Sales = 
   AVERAGEX(
       VALUES(Sales[Month]),
       CALCULATE(SUM(Sales[Sales]))
   )

• Format as Currency
• Set appropriate decimal places

5. Month-over-Month Change:

   MoM Change = 
   VAR CurrentMonth = CALCULATE(SUM(Sales[Sales]))
   VAR PreviousMonth = CALCULATE(
       SUM(Sales[Sales]),
       DATEADD(Sales[Date], -1, MONTH)
   )
   RETURN
   DIVIDE(
       (CurrentMonth - PreviousMonth),
       PreviousMonth,
       BLANK()
   )

• Format as Percentage
• Add conditional formatting

Creating Visualizations

1. Create a Column chart:

• Drag ‘Year’ to Rows
• Add ‘Month’ under Year for drill-down capability
• Add the following measures to Values:
  • Total Sales by Year
  • YoY Growth
  • Running Total
• Format your chart:
  • Turn on +/- buttons for drill-down
  • Enable row subtotals
  • Apply currency format to sales columns
  • Add conditional formatting to growth measures

1. Add Key Performance Indicators:

• Create a card visual for YTD sales
• Add a multi-row card showing:
  • Current year sales
  • Previous year sales
  • YoY Growth
• Format cards:
  • Add icons
  • Set appropriate colors
  • Enable tooltips

1. Create Trend Analysis:

• Add a line chart:
  • X-axis: Date hierarchy
  • Y-axis: Running Total
  • Color: Product
• Format the chart:
  • Add data labels
  • Customize line styles
  • Enable drill-through options

1. Monthly Comparison Visual:

• Create a column chart:
  • X-axis: Month
  • Y-axis: Total Sales by Year
  • Legend: Year
• Format settings:
  • Set clustered column layout
  • Add data labels
  • Customize colors

Troubleshooting Common Issues

When working with yearly calculations, watch out for:

• Blank or future dates affecting your measures
• Incorrect fiscal year settings impacting calculations
• Missing data points creating gaps in your analysis
• Time zone issues affecting date calculations

Fix these by:

• Using FILTER() to remove unwanted dates
• Properly setting up your date table
• Adding error handling in your measures
• Standardizing datetime formats

For more advanced date handling techniques, check out our guide on creating date hierarchies in Power BI.