storing values of changing sources

[Datendetektiv]

Hi everybody,

I'm new to Power BI and to the forum and I'm facing a problem.

I want to visualize the course of the financial data of my company. The data source is a accounting software that puts out numbers only of the current month (e.g. financial data of July). So monthly the document is manually exported and put in SharePoint. This Excel file is then implemented into Power BI via the web source.

Now I can access the data, do my calculations and visualize graphs. When the next month arrives (e.g. August) and the data is updated, again only the current data of one month is available but the old data isn't anymore. In this case only the values of August are imported, July is no longer in the Excel file.

That means I can't visualize the developement over the course of time! However the goal is to show the developement of the numbers in the past to see trends for the future.

Although possible, it is not an option to manually do adjustments in Power BI Desktop every month. This should automatically happen as the file in SharPoint is updated.

Is there a way to store the values of the previous months so they stay available even though the source is changing to the data of the next month?

Or has anybody another idea to solve this problem?

Thank you very much in advance!

Best regards
Datendetektiv

[christinepayton]

If you save one file per month you should be able to get more than one month at a time? This is generally what people use the SharePoint folder connector for, it lets you combine and display data from multiple files. 

What financial system is it? I have never heard of a financial system that only outputs the current month of data. 

[Datendetektiv]

Thank you for your quick reply christinepayton,

The company is quite small and the financial system is mostly used for salary accounting. It is called DATEV and unfortunately there is no direct interface between this Programm and Power BI. Because of that I export the data as an Excel file and save it on SharePoint. I don't want to adjust the Power BI Desktop file every month, so the secretary simply replaces the old document with the new one but gives it the same name. This way Power BI still implements the same source but the numbers are updated and the visualizations/ calculations stay the same with the new data.

Is there a way to have the current as well as the previous data available without manually modifying (adding new source; update diagrams; adjust relationships; etc.) every time?

Because ideally I would set up the Power BI once and afterwards am not included in the process anymore. The secretary can handle SharePoint and the rest happens automatically.

Thank you in advance!

Best regards

Datendetektiv

[christinepayton · Accepted solution]

Yes, you would use the SharePoint folder connector for this. You give it the site URL, then filter the query on the path of that particular folder, and expand - it will extract and combine all the files in the folder ongoing when it refreshes. So you'd have one file per month in there, it'd combine them all for you. There's a slew of YouTube videos out there on how to use the connector if you want a walkthrough-- 

[foodd]

Hello @Datendetektiv , and thank you for sharing a question with the Community.  This reply is informational. Please follow the decorum of the Community Forum when asking a question.

Please share your work-in-progress Power BI Desktop file (with sensitive information removed) and any source files in Excel format that fully address your issue or question in a usable format (not as a screenshot). You can upload these files to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then provide the file's URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!