Multiple excel docs that collect values of same topics for different areas - year

[Plopez13]

Hi everyone! 

• Im creating a PBI dashboard feeded by an assessment excel workbook with milestones (classified in a 3 leves hierarchy) that will be filled with values each 15 days.
  • In the future the workbook would also have other sheets to measure other aspects (for example, different people divided by their roles, all the ceremonies that have taken place, number of projects...) for each area. I need the configuration to be flexible.
• I need to scale this dashboard to more areas. I thought that the best option is to generate one workbook per year and area. 
  • The name of each workbook will identify the year and area.
  • The report will compare the same assessment for differents areas

• I will have all the files in one folder for each area in a sharepoint folder

• Which would be the best option to implement this?

• I will like to automatize the process for the incorporation of new docs (each year) and facilitate the update of topics (new topics/change names).
  • Maybe the ideal would be to have one workbook with all the topics that updates the workbook of each area or something like that.
• Each value is filled in a specific column based in an date in informal format. I divide de year in quarters, months and fortnights but I dont record the specific date. I need to set the date column (QuarterMonthFortnight - remember that each workbook is a year) in date format. Is it possible?

Thank you!

[Anonymous]

Hi @Plopez13 ,

It seems that you have multiple excel files, each holding a year's worth of data separately. These excel files are placed in the same folder at the same time. Now you want to create a dashboard based on this data that needs to be classified in a 3 hierarchy leves? Am I understanding correctly? If not, please correct me.

Do these excel files have the same structure please? How do you want to do the “updates” you mentioned? What is the format of the last mentioned date? Could you please provide us with some data, screenshots and background logic so that we can better understand your needs and give you the right solution?

---

Each value is filled in a specific column based in an date in informal format. I divide de year in quarters, months and fortnights but I dont record the specific date. I need to set the date column (QuarterMonthFortnight - remember that each workbook is a year) in date format. Is it possible?

---

Best Regards