Same project for different dates columns

[mwender]

Hi,
I'd like to create a dashboard in which I'll be able to see the capex and opex trend per quarter and per year for several activities (activity name).
The problem is that I need for each single project (1 raw), 4 columns for each quarter date, 4 columns for each actual spend, 4 columns for each planned spend and this for each type of spend (capex and opex).

In this format table, I found it difficult to create a report in excel or power BI since the dates are not in the same column.

When setting the table in a way that the date is in the same column, I got the same activity name duplicated by 8 which is not user friendly (each owner has to update the data)

Any suggestion for the best option to set up the database?

THANK YOU

 

[mwender]

Hi,
I'd like to create a dashboard in which I'll be able to see the capex and opex trend per quarter and per year for several activities (activity name).
The problem is that I need for each single project (1 raw), 4 columns for each quarter date, 4 columns for each actual spend, 4 columns for each planned spend and this for each type of spend (capex and opex).
View attachment 89916
In this format table, I found it difficult to create a report in excel or power BI since the dates are not in the same column.

When setting the table in a way that the date is in the same column, I got the same activity name duplicated by 8 which is not user friendly (each owner has to update the data)
View attachment 89917

Any suggestion for the best option to set up the database?

THANK YOU

Take into consideration that the database will sit in Sharepoint list for the users

 

[excel_learnerz]

One option to set up the database in a more user-friendly way would be to use a "normalized" database design, where you have separate tables for the different types of data you need to track (e.g. actual spend, planned spend, capex, opex). Each table would have a common key (e.g. project ID and date) that links to the other tables. This way, you can avoid duplicating the activity name for each quarter and each type of spend, and instead have a single record for each quarter and type of spend for each project.

For example, you could have the following tables:

1. Projects table: Contains information about each project, such as project ID, activity name, and owner.
2. Dates table: Contains a list of all the quarter-end dates you need to track, along with additional information such as quarter and year.
3. Actual spend table: Contains the actual spend data for each quarter and project, with columns for project ID, date, and actual spend.
4. Planned spend table: Contains the planned spend data for each quarter and project, with columns for project ID, date, and planned spend.
5. Capex table: Contains the capex data for each quarter and project, with columns for project ID, date, and capex spend.
6. Opex table: Contains the opex data for each quarter and project, with columns for project ID, date, and opex spend.

You can then create relationships between the tables based on the common key columns (e.g. project ID and date), and use queries or views to combine the data as needed for your reporting purposes.

This design will make it easier to update and maintain the data, as each record only needs to be entered once, and the relationships between the tables ensure that the data is consistent and accurate. It will also make it easier to create reports and visualizations, as you can use standard aggregation functions and pivot tables to summarize and analyze the data.

 

[mwender]

I like the option.
Yet since we'd like the users to add the info in 1 table only, I'm looking a way to set up table 1 (image1) as table 2 (image 2).
I tried to unpivot but it came up with multiples combinations for each activity while I need only 4 (1 for each Quarter)

 

[excel_learnerz]

In that case, you can create a single table with a slightly different structure to make it easier to analyze the data. You can use a single table with the following columns:

• Activity Name
• Owner
• Quarter (e.g., Q1 2023, Q2 2023, etc.)
• Capex Actual Spend
• Capex Planned Spend
• Opex Actual Spend
• Opex Planned Spend

For each project, you will have one row per quarter with the corresponding actual and planned spend for capex and opex. This way, you will have a maximum of 4 rows per project per year, and the data will be easier to manage and analyze.

To transform Table 1 into Table 2, you can follow these steps:

1. In Table 1, add a column for 'Quarter' to the left of the Capex Actual Spend column.
2. Fill the 'Quarter' column with the appropriate quarter value for each row (e.g., Q1 2023, Q2 2023, etc.) based on the column headers in Table 1.
3. Copy the entire range of Table 1 (including the new 'Quarter' column) and paste it into a new sheet as values only (using Paste Special > Values).
4. Select the entire range of the new sheet and use the 'Remove Duplicates' feature in Excel (Data > Remove Duplicates). In the 'Remove Duplicates' dialog, select all columns except for the 'Quarter' column, and click 'OK' to remove duplicate rows.
5. Now you should have a new table that is similar to Table 2 with one row per quarter for each project and separate columns for Capex Actual Spend, Capex Planned Spend, Opex Actual Spend, and Opex Planned Spend.

This new table format will allow you to create reports and visualizations more easily, as you can use standard aggregation functions and pivot tables to summarize and analyze the data.