Power Bi is combining historical data from excel

[jordan_h_RA3]

I am working with an excel workbook and utilizing a worksheet from it. The data it contains has current year (2024) data and historicals back to 2016. There are 3 date-associated columns labeled: date match (MM/DD/YYYY), year (YYYY), and month (text). When visualizing the data it is summing up all of the data and counting it towards the year 2024. And causing inflated totals for 2024. However, it is correct for a total count for all years of data. 

What I am looking to do is have the data categorized by year, in order to be able to filter the visual and list it by year. I would like to know what the best process to accomplish this.

[Anonymous · Accepted solution]

Hi @jordan_h_RA3 ,

This is my table, and it contains data from 2016 to 2024.

When I use the matrix, it will be classified by year and month. We could choose the calculation method we need. I use summation here.

Or we could also use it in combination with the slicer.

Could you please provide example data or sample files here if you have any confused? We could offer you more help if we have information in detail. And what you expect the output to be. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @jordan_h_RA3 -To correctly categorize your data by year and ensure the visualization reflects the data for each year accurately

Once you after of excel file to power bi using Get data option, go to the table view and cross check the fields:

date match → Date/Time.
year → Whole Number.
month → Text.
If the year column is not numeric, convert it using Data Type in the ribbon.Check that the year column is used as the primary slicer or category in your visualization.Drag the year column to the Axis or Legend field for visualizations like bar charts, line charts, or matrix tables.Ensure the appropriate column is used in the Values field.
Clicking the dropdown beside the column name in the Values section.Selecting Count, Sum, or another aggregation as appropriate.

Drag the year column into the Axis or Columns field in your chart.
Confirm that data is grouped correctly by year and no totals are inflated for 2024.

Reflinks:

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn

Using calculated columns in Power BI Desktop - Power BI | Microsoft Learn

Hope this details helps.