Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ianka08
Frequent Visitor

Calculating logged time per team, sprint and issue

‎08-18-2021 07:50 AM

I have a few teams that work on an Agile Scrum setting: They usually do 2 weeks’ sprints during which they work on the planned stories/tasks. The stories are estimated during every sprint planning in Story Points (each point corresponds to 1 person’s day of work). At the end of each day, each person has to register their worklogs (the number of hours spent working on each story/task) for each story/task.

I calculate my productivity at the end of each sprint by dividing the total of story points delivered during that sprint (i.e. stories resolved in that sprint) between the number of logged hours during that sprint.

I have 3 relevant tables:

The worklogs table that has the worklogs data (issue key, entry date, logged time)

The issues table that contains all of the tickets, their story points and their epics

The sprints table that contains all the sprints, their start and end date

All three tables are connected by the Issue key.

I need to find the logged time per team per sprint per issue.

 

Here's a sample of the sprint table:

ianka08_1-1629293446826.png

 

Issues table :

ianka08_2-1629293483998.png

 

Worklogs table :

ianka08_3-1629293643538.png

 

Here's how they relate to each other :

ianka08_4-1629297982648.png

Any ideas as to how I could go about this?

 

 If anyone has any ideas, I'm a taker. Thanks in advance.

Labels:
Message 1 of 7
531 Views
0
6 REPLIES 6
ianka08
Frequent Visitor

‎08-23-2021 05:09 AM

Sprints table:

 

CléSprint NameSprint Start DateSprint End Date
SS-4774Sprint 49 Next15/02/202126/02/2021
SS-4783Sprint 49 Next15/02/202126/02/2021
SS-4812Sprint 49 Next15/02/202126/02/2021
SS-4813Sprint 49 Next15/02/202126/02/2021
SS-4955Sprint 49 Next15/02/202126/02/2021
SS-4983Sprint 49 Next15/02/202126/02/2021
SS-4984Sprint 49 Next15/02/202126/02/2021
SS-5007Sprint 49 Next15/02/202126/02/2021
SS-5008Sprint 49 Next15/02/202126/02/2021
SS-5017Sprint 49 Next15/02/202126/02/2021
SS-2067Sprint 49 TVS15/02/202126/02/2021
SS-4631Sprint 49 TVS15/02/202126/02/2021
SS-4733Sprint 49 TVS15/02/202126/02/2021
SS-4766Sprint 49 TVS15/02/202126/02/2021
SS-4769Sprint 49 TVS15/02/202126/02/2021
SS-4771Sprint 49 TVS15/02/202126/02/2021
SS-4867Sprint 49 TVS15/02/202126/02/2021
SS-4892Sprint 49 TVS15/02/202126/02/2021
SS-4893Sprint 49 TVS15/02/202126/02/2021
SS-4894Sprint 49 TVS15/02/202126/02/2021
SS-4981Sprint 49 TVS15/02/202126/02/2021
SS-4987Sprint 49 TVS15/02/202126/02/2021
SS-4990Sprint 49 TVS15/02/202126/02/2021
SS-5024Sprint 49 TVS15/02/202126/02/2021
SS-4459Sprint 49 TST15/02/202126/02/2021
SS-4476Sprint 49 TST15/02/202126/02/2021
SS-4578Sprint 49 TST15/02/202126/02/2021
SS-4636Sprint 49 TST15/02/202126/02/2021
SS-4755Sprint 49 TST15/02/202126/02/2021
SS-4756Sprint 49 TST15/02/202126/02/2021
SS-4777Sprint 49 TST15/02/202126/02/2021
SS-4778Sprint 49 TST15/02/202126/02/2021
SS-4789Sprint 49 TST15/02/202126/02/2021
SS-4804Sprint 49 TST15/02/202126/02/2021
SS-4814Sprint 49 TST15/02/202126/02/2021
SS-4815Sprint 49 TST15/02/202126/02/2021
SS-4816Sprint 49 TST15/02/202126/02/2021
SS-4818Sprint 49 TST15/02/202126/02/2021
SS-4820Sprint 49 TST15/02/202126/02/2021
SS-4862Sprint 49 TST15/02/202126/02/2021
SS-4458Sprint 49 VMSH15/02/202126/02/2021
SS-4516Sprint 49 VMSH15/02/202126/02/2021
SS-4602Sprint 49 VMSH15/02/202126/02/2021
SS-4612Sprint 49 VMSH15/02/202126/02/2021
SS-4694Sprint 49 VMSH15/02/202126/02/2021
SS-4713Sprint 49 VMSH15/02/202126/02/2021
SS-4740Sprint 49 VMSH15/02/202126/02/2021
SS-4751Sprint 49 VMSH15/02/202126/02/2021
SS-4797Sprint 49 VMSH15/02/202126/02/2021
SS-4827Sprint 49 VMSH15/02/202126/02/2021
SS-4828Sprint 49 VMSH15/02/202126/02/2021
SS-4856Sprint 49 VMSH15/02/202126/02/2021
SS-4896Sprint 49 VMSH15/02/202126/02/2021
SS-4901Sprint 49 VMSH15/02/202126/02/2021
SS-4908Sprint 49 VMSH15/02/202126/02/2021
SS-4909Sprint 49 VMSH15/02/202126/02/2021
SS-4913Sprint 49 VMSH15/02/202126/02/2021
SS-4919Sprint 49 VMSH15/02/202126/02/2021
SS-4920Sprint 49 VMSH15/02/202126/02/2021
SS-4921Sprint 49 VMSH15/02/202126/02/2021
SS-4930Sprint 49 VMSH15/02/202126/02/2021
SS-4884Sprint 50 Next01/03/202112/03/2021
SS-5057Sprint 50 Next01/03/202112/03/2021
SS-5058Sprint 50 Next01/03/202112/03/2021
SS-5065Sprint 50 Next01/03/202112/03/2021
SS-5099Sprint 50 Next01/03/202112/03/2021
SS-5139Sprint 50 Next01/03/202112/03/2021
SS-5141Sprint 50 Next01/03/202112/03/2021
SS-2067Sprint 50 TVS01/03/202112/03/2021
SS-3451Sprint 50 TVS01/03/202112/03/2021
SS-3490Sprint 50 TVS01/03/202112/03/2021
SS-4291Sprint 50 TVS01/03/202112/03/2021
SS-4414Sprint 50 TVS01/03/202112/03/2021
SS-4517Sprint 50 TVS01/03/202112/03/2021
SS-4591Sprint 50 TVS01/03/202112/03/2021
SS-4592Sprint 50 TVS01/03/202112/03/2021
SS-4631Sprint 50 TVS01/03/202112/03/2021
SS-4645Sprint 50 TVS01/03/202112/03/2021
SS-4968Sprint 50 TVS01/03/202112/03/2021
SS-4973Sprint 50 TVS01/03/202112/03/2021
SS-4978Sprint 50 TVS01/03/202112/03/2021
SS-4981Sprint 50 TVS01/03/202112/03/2021
SS-4990Sprint 50 TVS01/03/202112/03/2021
SS-4992Sprint 50 TVS01/03/202112/03/2021
SS-4999Sprint 50 TVS01/03/202112/03/2021
SS-5003Sprint 50 TVS01/03/202112/03/2021
SS-5038Sprint 50 TVS01/03/202112/03/2021
SS-5050Sprint 50 TVS01/03/202112/03/2021
SS-5059Sprint 50 TVS01/03/202112/03/2021
SS-5092Sprint 50 TVS01/03/202112/03/2021
SS-5095Sprint 50 TVS01/03/202112/03/2021
SS-4965Sprint 50 TST01/03/202112/03/2021
SS-4989Sprint 50 TST01/03/202112/03/2021
SS-5015Sprint 50 TST01/03/202112/03/2021
SS-5016Sprint 50 TST01/03/202112/03/2021
SS-5045Sprint 50 TST01/03/202112/03/2021
SS-5062Sprint 50 TST01/03/202112/03/2021
SS-5066Sprint 50 TST01/03/202112/03/2021
SS-5135Sprint 50 TST01/03/202112/03/2021
SS-2602Sprint 50 VMSH01/03/202112/03/2021
SS-3820Sprint 50 VMSH01/03/202112/03/2021
SS-4916Sprint 50 VMSH01/03/202112/03/2021
SS-4919Sprint 50 VMSH01/03/202112/03/2021
SS-4921Sprint 50 VMSH01/03/202112/03/2021
SS-4972Sprint 50 VMSH01/03/202112/03/2021
SS-4988Sprint 50 VMSH01/03/202112/03/2021
SS-4993Sprint 50 VMSH01/03/202112/03/2021
SS-5036Sprint 50 VMSH01/03/202112/03/2021
SS-5078Sprint 50 VMSH01/03/202112/03/2021
SS-5080Sprint 50 VMSH01/03/202112/03/2021
SS-5084Sprint 50 VMSH01/03/202112/03/2021
SS-5087Sprint 50 VMSH01/03/202112/03/2021
SS-5105Sprint 50 VMSH01/03/202112/03/2021
Message 7 of 7
321 Views
0
V-lianl-msft
Community Support
Community Support

‎08-19-2021 10:39 PM

Hi @ianka08 ,

 

Could you provide sample files after remove sensitive data?

How-to-provide-sample-data-in-the-Power-BI-Forum 

In my opinion, this type of problem is similar to this one,See if it will help you:

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr... 

 

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

Message 2 of 7
442 Views
0
ianka08
Frequent Visitor
In response to V-lianl-msft

‎08-20-2021 03:29 AM

Hi Liang,

Thanks for your reply. I checked the similar problem you shared, but I don't see how it would apply to my case. 

You'll find below the requested sample tables.

 

 

Message 3 of 7
425 Views
0
ianka08
Frequent Visitor
In response to ianka08

‎08-23-2021 08:06 AM

I tried to copy here the  sample tables, but I kept getting different errors and when I managed to copy it, the format was all messed up. So, I'll just share a wetransfer link where you can download the sample. It's 3 excel spreadsheets. The link below : 

https://we.tl/t-jbGYXwPtd7

 

Let me know if that works for you or if I can share them in a more convient way for you. 

 

Message 4 of 7
361 Views
0
aj1973
Community Champion
Community Champion
In response to ianka08

‎08-23-2021 08:47 AM

Hi @ianka08 

Is this an example on how you want to look at your data?

aj1973_0-1629733621906.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Message 5 of 7
341 Views
0
ianka08
Frequent Visitor
In response to aj1973

‎08-25-2021 02:23 AM

Hi @aj1973 ,

Not quite. I'd expect to see for each sprint the total logged time based on sprint start date and sprint end date. So, in your exemple for Sprint 49 TVS I'd see in the Time Entry Logged Time column 126,98 (which is the sum of the logged times from 15/02/21 (sprint start date) to 26/02/20 (sprint end date).

The logged time from 01/03/2021 to 12/03/2021 should appear in the Sprint 50 TVS (for which start and end dates are 01/03/2021 and 12/03/2021 respectively). And the logged time of 9/4/2021 should appear in the  Sprint 52 TVS (which has start and end date from 29/03/2021 to 10/04/2021). 

 

Hope that clarifies my request.

 

Regards,

 

Iana

 

Message 6 of 7
282 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All