Forum Discussion

pva's avatar
pva
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
pva
Frequent Visitor
4 years ago

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

 

IdProyectSprint numberTotal hoursDone Hours
12Sprint 1107
12Sprint 1124
12Sprint 252
12Sprint 273
13Sprint 12010
13Sprint 197
13Sprint 231

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

IdProyectSprint NumberTotal HoursDone hoursRemaining from past sprint
12Sprint 127130
12Sprint 212514 (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's avatar
    amitchandak
    Super User
    4 years ago

    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