Forum Discussion
Monthly Project Income Summary with weekly detailed overview
Hi community,
I'm having trouble to aggregate data for my financials report and to show it in a detailed, yet summarizing manner.
First, I'll start with explaining how the dataset is structured to proceed at where I think it needs to be:
Financials DB:
Date - WeekNumber - Month - Project - Employee - Type - Hours Spent - Theoretical Income
01/01/2020 - 1 - January - Project1 - John Doe - Project Management - 1 - 60
08/01/2020 - 2 - January - Project1 - John Doe - Project Management - 1 - 60
08/01/2020 - 1 - January - Project1 - Jane Doe - Project Execution - 4 - 50
08/01/2020 - 2 - January - Project1 - Jane Doe - Project Execution - 4 - 50
15/01/2020 - 3 - January - Project1 - Jane Doe - Project Execution - 4 - 50
22/01/2020 - 4 - January - Project1 - Jane Doe - Project Execution - 7.2 - 90
Project DB:
Month - Project - Expected TM Income - Expected PM Income
January - Project1 - 240 - 120
The way I would like to present this data is as following:
This is being done in order to show weekly progress of the income received towards the expected monthly goal per Project for both PM & Project Execution. The expected numbers are fixed on the contract, the hours spent are calculated based on hourly price (resulting in sometimes more or less being done).
At the moment, I have tried this by grouping the data into a "weekly theoretical income" table maintaining an identifier of Month_Project with colums Type & Theoretical Income, yet I can't find:
(1) how it would be possible to stack the different weeks on eachother, while keeping the weekly axis and
(2) calculate on each week-row how much has been earnt previously in that month to make the "Monthly Income to be achieved" variable.
Thank you a lot in advance
2 Replies
Please provide more sample data - across multiple months. Do your months share week numbers? Do you have a proper Calendar table with all releavant fiscal calendar columns?
Please show the expected outcome across months.
Hello Ibendlin,
Thank you for your response.
Below you can find more sample data: (I've also put it in a table so it's easyer copy/pastable)
Date WeekNumber Month ProjectName Employee WorkingType Hours spent Calculated Income 01/01/2020 1 January Project1 John Doe PM 1 60 08/01/2020 2 January Project1 John Doe PM 1 60 01/01/2020 1 January Project1 Jane Doe PE 4 50 08/01/2020 2 January Project1 Jane Doe PE 4 50 01/02/2020 5 February Project1 John Doe PM 2 120 08/02/2020 6 February Project1 Jane Doe PE 4 50 08/02/2020 6 February Project1 John Doe PM 1 60 15/02/2020 7 February Project1 Jane Doe PE 4 50 To respond to your question if I have a proper calendar table with all relevant fiscal colums, at the moment I do not, but I suspect your question is suggestive so I will look deeper into this to add this to the report.
As for an expected outcome across multiple months, the desired result should have a result more based on the differentiation between PM & PE. This graph can be seen as below previewed, and I have also updated the mock (weekly view) to the updated data:
1. Updated Weekly Mock
2. Monthly Income Overview:
I hope this information is sufficient, and if you would have any other suggestions or requirments to respond properly, please let me know and I will respond as good as possible.
Thank you for the effort.
