Total completed work for work item

[andrekroker]

Folks,
Considering the following table, and the statements below:

• Every day, the base is updated. Yesterday date becomes false, and a new line for current day is created with ‘Is Current’ = True
• This means that, the completed work for each work item just refreshes every day. Completed work should not be read as a sum of the field throughout all days.

Sprint	Sprint End Date	Sprint Start Date	Work Item Id	State	Work Item Type	Completed Work	Date	Is Current
Sprint 6	25-May	18-May	23451	Active	Task	5	18-May	False
Sprint 6	25-May	18-May	23810	Active	Task	4	18-May	False
Sprint 6	25-May	18-May	23810	Active	Task	4	19-May	True
Sprint 6	25-May	18-May	23451	Active	Task	6	19-May	True
Sprint 5	18-May	11-May	23451	New	Task	0	11-May	False
Sprint 5	18-May	11-May	23451	New	Task	0	12-May	False
Sprint 5	18-May	11-May	23451	Active	Task	0	13-May	False
Sprint 5	18-May	11-May	23451	Active	Task	0	14-May	False
Sprint 5	18-May	11-May	23451	Active	Task	0	15-May	False
Sprint 5	18-May	11-May	23451	Active	Task	0	16-May	False
Sprint 5	18-May	11-May	23451	Active	Task	0	17-May	False
Sprint 5	18-May	11-May	23810	Active	Task	0	14-May	False
Sprint 5	18-May	11-May	23810	Active	Task	0	15-May	False
Sprint 5	18-May	11-May	23810	Active	Task	0	16-May	False
Sprint 5	18-May	11-May	23810	Active	Task	0	17-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	12-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	13-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	14-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	15-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	16-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	17-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	18-May	False
Sprint 5	18-May	11-May	23815	Closed	Task	4	19-May	True
Sprint 4	11-May	04-May	23451	New	Task	0	05-May	False
Sprint 4	11-May	04-May	23451	New	Task	0	06-May	False
Sprint 4	11-May	04-May	23451	New	Task	0	07-May	False
Sprint 4	11-May	04-May	23451	New	Task	0	08-May	False
Sprint 4	11-May	04-May	23451	New	Task	0	09-May	False
Sprint 4	11-May	04-May	23451	New	Task	0	10-May	False

I want to know:

1. What is the total amount of completed work for each work item.
   I´ve came up with this: 
   Total Completed Hours = CALCULATE(SUM(Table[Completed Work]), LASTDATE(Table[Date]))
   Is it correct or appropriate?
   
   
2. What is the total amount of completed work for each work item for each sprint.

[v-zhenbw-msft · Accepted solution]

Hi @andrekroker ,

We can use the following measure to meet your requirement.

the total amount = CALCULATE(SUM('Table'[Completed Work]), FILTER('Table','Table'[Date]<TODAY()))

You can create a table visual like this, if you want to calculate the total amount of completed work for each work item.

You also can create a table visual like this to calculate the total amount of completed work for each work item for each sprint.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[andrekroker]

Thank you a lot!