Creating a report to display differences between values in 2 files.

[iGWizard]

Hi,

I need to create a report that allows me to compare differences between two data files (this month and last month). The data files are essentially unit sales .csv files. They're both structured in the same way, with products as rows and months as columns across the top.

The files contain 36 months worth of data. The only difference being the more recent file contains a more recent month (lets say Janurary), and the older file contains one month behind (December). 

I want to be able to sum up the units for each row and then compare the differences between the 2 files. (e.g. 0 if they match, or perhaps -5000 if there was a change in -5000 reported units over the last month for a particular product).

I need to calculate the differences only for matching months within the files. So I will be ignoring the oldest month in last months file, and ignoring the latest month in the newest file. And calculating the difference in sales units this way.

I've decided to link the report to a sharepoint folder by using power automate, so when I upload a file in to this sharepoint folder, the report will be refreshed and display the newest files data in the 'new data' position. And what was 'new data' will be shifted over to the 'old data' position.

I'm ok with setting up the connection to the sharepoint folder. But how do I go about creating the report to:

1. calculate the differences between only the matching months within both sales unit files.

2. ensure what was the 'new data' shifts over to 'old data' position seamlessly. (second most recent file in the sharepoint will be 'old data' at all times). Along with most recent file being placed in the 'new data' position.

Any help will be much appreciated. Many thanks

[speedramps]

Try this in Power Query .... 

add a "version" column  to each table and populate is with "old" and "new" accordingly.

Then unpivot each table, so it converts the monthly columns to rows.

So instead of having a fat tables, you have a long thin tables.

Dont worry, it will not increase storage because of how Power BI  compression works.

Then change the month column to start of month date.

Merge both tables.

It is now easy to use Power Query or DAX to create a report with a "old", "new" and difference column for each month and filter just those that have differences.

Remember. If you ever receive data in columns for months, products, customers or countries then always consider unpivoting them to rows.  It makes lPower Bi so much easier !

If you struggle with these instructions then please share sample data as text (not a screen print) so we can import it and build a solution. You can share data using the table icon menu in this chat, or PBIX or Excel in Onedrive, Google drive or Dropbox.  Take care not so share private data.  Keep data simple, only share columns that are needed for the question and please use friendly column names.

Please click thumbs up for this help, 👍

and accept solutioon if it works. 😀

[Akash_Varuna]

Hi @iGWizard , Could you please try these 

1. Connect to SharePoint Folder: Load the two most recent files into Power BI.

2. Filter Data: Use Power Query to remove the oldest month in the previous file and the latest month in the current file.

3. Unpivot and Merge: Unpivot months into rows, merge datasets on Product and Month, and calculate the difference (New Units - Old Units).

4. Automate Refresh: Use Power Automate to manage SharePoint files and set up incremental refresh in Power BI.
   If this post helped please do give a kudos and accept this as a solution 
   Thanks In Advnace