Power BI solution for replacing separate SharePoint Folders

[Graemess]

My apologies for the length of the post. I am hoping for advice on the question at the end.

1. I work for a service provider that manages ICT shared services for about 60 different entities.
2. Each entity has a unique suite of services. My team is responsible for the monthly invoicing of the entities. The monthly process for charging each of the vendor services in summary is: (using mobile phones as an example)
3. Rename the previous month's Excel service chargeback template to this month.
4. Download the mobile phone charges CSV from the mobile service vendor's portal.
5. Download the Employee Establishment Report that details the active employees of the 60 entities.
6. Add this month's CSV and Active Employee data to the Monthly Excel template.
7. The Monthly Excel template has a Summary and Detail tab with 60 entities under 12 overarching Portfolios.
8. The template maps the individual user charges to one of the 60 entities on the Detail tab with the totals for the entities and Portfolios on the Summary tab.
9. Next step is to group the Summary and Detail tabs - choose Move or Copy, copy to (new book) break links and name it  'Mobile Phone 202405 - All' .
10. We copy the file 12 times, manually filter and delete all references to other Portfolios and save the file under the Portfolio name. Rinse & repeat for all 12 Portfolios.
11. We have a SharePoint setup that has a folder for each Portfolio, with subfolders for each service provider. We place a copy of each 'Mobile Phone 202405 - Portfolio Number 1 of 12' into the Mobile Phone folder for each Portfolio.
12. Previously we have been providing the Summary and Detail for the 12 Portfolios. For the coming financial year they want us to create a separate file for each of the 60 entities and place them in the vendor subfolders of each entity.
13. We have around 20 Monthly Excel template processes.
14. We have two people to do this and the number of separate mouse clicks and movements will end in occupational injuries.
15. I have created a PowerBI that pulls the data and displays tables for Summary and Detail to resemble the Monthly Excel template.
1. I plan to create a separate view for each entity based on user's permissions & permit a download to Excel.

Questions

I am hoping for a brief summary solution and some links to tutorials

1. How can I set up PowerBI to enable historical access for each month of the year and an archive for previous years?

[Graemess]

Thank you @MFelix,

I probably have not articulated this very well. We are not really distributing invoices but providing Monthly Excel Summary and Detail statements to the entities for download.
What I was thinking was:
1) replacing all the 60 entity folders and subfolders with a single front end.
2) The front end would be a PowerBI dashboard.

3) All of the users would have a view restricted to the entity that they belonged to.

4) They could download the Excel statement file.

5) There could be a bar chart that had the previous months totals.

6) They could access any of the the previous month's statements by clicking on the bar chart.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from@MFelix,, please allow me to provide another insight:

Hi,@Graemess 

Have your problems been solved? If you find a solution, feel free to share it with us, which will help other community members with the same problem find a solution faster.

Regarding the issue you raised, my solution is as follows:

Depending on your needs, you can try using the date column as a slicer and using the directquery storage mode to store the data.

1.First you can create a date table using the following calculation table:

main Table = CALENDAR(DATE(2024,3,19),TODAY())

Select the date hierarchy you want as the slicer.

Here is the relevant documentation:

Select the date hierarchy you want as the slicer.

Here is the relevant documentation:

Auto date/time in Power BI Desktop - Power BI | Microsoft Learn

Slicers in Power BI - Power BI | Microsoft Learn

2.Next, establish relationships with the tables you need to control:

Here is the relevant documentation:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

This makes it possible to use only the data selected by the slicer.

3.Here are the final results:

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Graemess]

Thank you so much for your reply and proposed solution v-linyulu-msft!
I am sorry I missed your original reply and the follow up for some reason.
I only get a small window to go back and experiment with Power BI as my bosses are so risk averse they will not entertain any solution that requires tool from this century.
I have not got time to go back to this right now but I hope to test the proposal before the end of next week.
My apologies for the delay. If I don't get time to look into it would it be better to accept the solution and ask another question later of leave it open?

Preview

[MFelix]

Hi @Graemess ,

Not sure if you are in the correct forum for this, Power BI is not used to create files or invoicing, it's used for data analytics, that can be automated using several different sources.

In this case I believe that you need an RPA proccess using Power Automate that will allow you to automate the full process from the download of the different invoicing entities to the archiving and saving new data.

https://powerautomate.microsoft.com/en-us/what-is-rpa/

https://learn.microsoft.com/en-us/training/paths/work-automation-flow/