Forum Discussion
Calculating remaining hours
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!!
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.
4 Replies
pva , Create these columns
Rank = rankx(filter(Table, [IdProyect] =earlier([IdProyect])) , [Name],,asc,dense)
Remainig hours = if([Rank]=1, 0, [Total Hours] - [Done Hours] )
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.
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
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.
