Monthly Project Income Summary with weekly detailed overview

[MaximeG]

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

[MaximeG]

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.

[lbendlin]

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.