Cumulative Report / Project Hours

[JS27]

Hello Guys, I'm new in this forum and working with PowerBI, i would like to have some advices in how to handle the following scenario using Cumulatives Totals for my Projects hours and reduce every month the hours executed by my team, according to the budgets time for each project:

I have these tables in Smartsheet:

Budget: This table brings the estimated hours for my projects, Budget Hours is what's we supposed to executed totally and Budget Hours Not Prof is Contigency Budget if anything happens. Everytime i have a new project i go to this sheet and put the information related. This table does not change.

		Budget
Project Name	Project Consultant	Budget Hours	Budget Hours Not Prof
Project1	Mike James	100	20
Project2	George Michael	200	18

Activity Report: This table brings me the project daily reports, hours executed, details, etc.  

		Activity Report
Project Name	Activity	Date	Hours Exec	Hours Not Prof Exec
Project1	Project Planning	15-05-2020	5	1
Project1	Project Planning	17-05-2020	10	0
Project1	Project Execution	14-06-2020	20	0
Project1	Project Execution	15-06-2020	0	9
Project2	Project Planning	15-05-2020	10	2
Project2	Project Execution	15-06-2020	15	4

I would like to have a Matrix or Table that can reduce from my budget each month the reports introduced it per Project. For example:

Desire Table:

			Backlog Time
Project Name	Project Consultant	Budget Hours	Budget Hours Not Profitable	Hours Exec  May - 2020	Hours Not Prof Exec May - 2020	Backlog Hours Exec May 2020	Backlog Hours Not Prof Exec May 2020	Hours Exec  Jun - 2020	Hours Not Prof Exec Jun - 2020	Backlog Hours Exec Jun 2020	Backlog Hours Not Prof Exec Jun 2020
Project1	Mike James	100	20	15	1	85	19	20	9	65	10
Project2	George Michael	200	18	10	2	190	16	15	4	175	12

I create a relationship between the Project Name for the 2 tables, and create a cummulative formula that summarized me the hours being executed month by month but it doesnt work the way i want it.

Cumulative sum = CALCULATE(SUM('Activity Report'[Hours Exec]),FILTER(ALL('Activity Report'),[Date]<=MAX([Date])),VALUES('Activity Report'[Project Name]))

Any suggestions about the measures or the way that i should follow to get this done?

Thanks in Advance.

[v-yingjl · Accepted solution]

Hi @JS27 ,

To get exactly the same output as you expected, it could be complicated and create multi simliar measures:

Hours Exec May 2020 =
CALCULATE (
    SUM ( 'Activity Report'[Hours Exec] ),
    FILTER ( 'Activity Report', 'Activity Report'[Date].[MonthNo] = 5 )
)

Hours Not Prof Exec May-2020 =
CALCULATE (
    SUM ( 'Activity Report'[Hours Not Prof Exec] ),
    FILTER ( 'Activity Report', 'Activity Report'[Date].[MonthNo] = 5 )
)

Backlog Hours Exec May 2020 = SELECTEDVALUE('Budget'[Budget Hours]) - [Hours Exec May 2020]

Backlog Hours Not Prof Exec May 2020 = SELECTEDVALUE(Budget[Budget Hours Not Profitable]) - [Hours Not Prof Exec May-2020]

Sample file is attached:  Cumulative Report / Project Hours.pbix

Best Regards,
Yingjie Li

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

[JS27]

That's works perfectly @v-yingjl , thank you very much! 

One thing, Is it possible to have a backlog total for each month not taking into consideration the Project in Detail? I mean, trying to minimize the amount of measures that i need to create to get this done, using the same dataset. 

Something like:

Month	Backlog Hours	Backlog Not Profitable
May 2020	275	35
June 2020	240	22

Regards.

[amitchandak]

@JS27 , create a project dimension, join both with the project and use project from project

Measure =
CALCULATE(Budget(Budget[Hour]),filter(Budget,Budget[Project] <=max(Project[Project]))) - CALCULATE(SUM(Activity[Hour]),filter(date,date[date] <=max(Activity[Date])))