Calculating remaining hours

[pva]

Hello everyone, I have a table like this where we have how many hours have every sprints and the hours that were done during them.

Id	Name	Total hours	Done hours
12	Sprint 1	27	17
12	Sprint 2	11	9
13	Sprint 1	34	12
13	Sprint 2	23	9

What I want is to create a calculated column that shows how many hours remains from the last sprint. It should be like this:

IdProyect	Name	Total hours	Done hours	Remainig hours from last srpint
12	Sprint 1	27	17	0 (cause it's the first one)
12	Sprint 2	11	9	10
13	Sprint 1	34	12	0 (cause it's the first one)
13	Sprint 2	23	9	14

Is there any way to do so?

Thank you very much!!

[Anonymous · Accepted solution]

Hi @pva ,

There seems to be a problem with the data you provided, if grouped by IdProyect and Sprint number fields, the corresponding sum should be 10+12 and 7+4. I did a test and refer to the following code.

code:
Rank =
RANKX (
    FILTER ( ALL ( 'Table' ), [IdProyect] = EARLIER ( [IdProyect] ) ),
    [Sprint number],
    ,
    asc,
    DENSE
)

Remainig hours = 
var tour = CALCULATE(SUM('Table'[Total hours]),ALLEXCEPT('Table','Table'[IdProyect],'Table'[Sprint number]))
var done = CALCULATE(SUM('Table'[Done Hours]),ALLEXCEPT('Table','Table'[IdProyect],'Table'[Sprint number]))
return
if('Table'[Rank]=1, 0 , CALCULATE(tour-done,FILTER(ALL('Table'),'Table'[IdProyect]=EARLIER('Table'[IdProyect]) && 'Table'[Sprint_number]<EARLIER('Table'[Sprint_number]))))

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Stephen

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

[pva]

Thanks for your quick response. I made a mistake asking my question. The table that I have is like this.

IdProyect	Sprint number	Total hours	Done Hours
12	Sprint 1	10	7
12	Sprint 1	12	4
12	Sprint 2	5	2
12	Sprint 2	7	3
13	Sprint 1	20	10
13	Sprint 1	9	7
13	Sprint 2	3	1

And I'm trying to make a table like this in the report. Example filtered by Proyect 12

IdProyect	Sprint Number	Total Hours	Done hours	Remaining from past sprint
12	Sprint 1	27	13	0
12	Sprint 2	12	5	14 (27-13)

Do you think is this possible?

Thanks again!! @amitchandak I'm trying to clarify it. There is not rank = 1 in the calculated column.

[amitchandak]

@pva , Try like

Rank = rankx(filter(Table, [IdProyect] =earlier([IdProyect])) , [Name],,asc,dense)

Remainig hours = if([Rank]=1, 0, Sumx(filter( Table,[IdProyect] =earlier([IdProyect]) && [Name] <= earlier([Name]) ),  [Total Hours] - [Done Hours] )

Sum up rank one onward

[amitchandak]

@pva , Create these columns

Rank = rankx(filter(Table, [IdProyect] =earlier([IdProyect])) , [Name],,asc,dense)

Remainig hours = if([Rank]=1, 0, [Total Hours] - [Done Hours] )