Power BI - is there a way to sum one column in the tables across the several identical monthly repor

[Anonymous]

I am struggling in finding a solution to my problem since a few months now, I created a report that is refreshed with a new set of data every month. The table that contains the data that are refreshed every month has a structure similar to the table in the picture.

Since with the refresh we lose customer key identifiers for internal responsibility switch, my rolling 12 months figures in the refreshed table do not represent exactly the picture of our sales in the past 12 months, because we lose the sales we accomplished with customers that are now served by other teams within the company.

In order to avoid such problem, I can sum up monthly sales in each of my monthly report but it is here that I am struggling:

Is there a way to link the current month report with previous month report and sum up the column with monthly sales figures?

[Anonymous · Accepted solution]

Hi @Anonymous ,

I'd like to suggest you add a custom column in query editor with Folder.Files function to extract 'Date modified' from detail file attributes. 

Power Query – List All the Files in a Folder & File Attributes 

Sample formula:

#"Added Custom" = Table.AddColumn(#"previous step", "Date modified", each Table.SelectRows(Folder.Files("C:\Users\xxxxx\test\"),each [Name]="Test.csv")[Date modified]{0})

This date should be the overwritten date, you can use this to clarify the end date for rolling records and use offset of your records to get correspond month.

Notice: Folder connector should get data from the folder that you store the CSV file and filtered by your file name.

Regards,

Xiaoxin Sheng

[Anonymous]

thanks Xiaoxin, I will definitely look more into power query functionalities. 

Your hint will definitely help more people.

[Mariusz]

Hi @Anonymous 

Where does your data come from?

Many Thanks

Mariusz

[Anonymous]

Hi Mariusz, 

it comes from a csv file that I store in a tdrive, and the file is overwritten monthly with new data.

@Mariusz 

[Anonymous]

That said, the source of previous months data has to be powerbi report regarding the previous month.