Comparing Team Capacity (Excel) with Hybrid Workload (Project + Squad Field) by Quarter

Questions & Answers
, , ,
1

Hi everyone,

I am looking for the best approach to build a capacity vs. workload report that compares planned figures from an external Excel file with actual Jira issues. My goal is to see a side-by-side comparison of a team’s total capacity and the total number of issues created for that team within a specific quarter.

The Context & Setup:

  • Capacity Data: I have an Excel file containing numerical “Planned Capacity” per User, per Team, and per Quarter (stored as text strings like “Q2-2025”).

  • Flexible Capacity Allocation: A crucial part of our setup is that a single user can support multiple teams within the same quarter, with different capacity values assigned to each team. The report needs to reflect these individual allocations correctly.

  • Hybrid Team Workload: Our team structure in Jira is not one-dimensional. Each team has a dedicated Jira Project, but they also handle issues from other projects where their “Squad” custom field is tagged. Therefore, a team’s workload is: (Issues in their Project) + (Issues in other projects with their Squad field set).

  • The Goal: I want a report organized by Quarter and Team that shows:

    1. The Planned Capacity imported from Excel for that team/quarter.

    2. The Issues Created count, unified from both their dedicated project and the “Squad” field.

I need a way to unify these two different Jira sources (Project and Squad field) into a single “Team” dimension that matches my Excel team names. Additionally, I need to align Jira’s creation date with the text-based quarter strings from my Excel so all data points land in the same column.

I am completely open to any suggestions, whether it involves specific MDX calculated measures, advanced data mapping, or creating a new calculated dimension to bridge these sources. I’ve attached a screenshot of my Excel structure for reference.

image
image1628×1074 108 KB

Thank you in advance for your guidance!

2

Hello @UmutEkinAL

Thanks for posting your question! I have a few follow-up questions and one suggestion before I can proceed with suggesting the steps to perform.

First, a clarification on terminology. Your Excel screenshot only shows a “Squad” column, but in your description you mention that each team has a dedicated Jira project. I assume Squad and Team refer to the same thing in your setup - could you confirm? It would help to settle on one term (either Squad or Team) so we don’t mix them up later.

I dont fully understand this part - Hybrid Team Workload

Question 1: Is the Squad field always filled in, including for issues in the team’s own dedicated project? And does it use the exact same team names as your Excel file?

Question 2: Is the Squad field a single-select custom field, or can an issue have multiple Squads?

One suggestion: in your Excel file, instead of storing quarters as text strings like “Q2-2025”, use an actual date - for example, the last date of the quarter (2025-06-30 for Q2 2025). That way eazyBI can map the value directly to Time dimension, and you’ll be able to filter and group the report by Quarter without any additional conversion logic. Time dimension will not recognize the Strings in you excel file.

Best,

Elita from support@eazybi.com