Forum Discussion
PowerBI Jira Connector - Issues Per Assignee over Time
Hello,
I'm trying to build a dashboard that tracks some team KPIs, including how many tickets an assignee has at any given time, tracked throughout history. I have a history_status table with fields including History Field = assignee and History New Value = "bob" History New Value Start = 1/15/2025. I want to know how many tickets "bob" has assigned at any given time, but this should also include when tickets are moved to status "done". This would also be in the same History Status table, History field = Status, History New Value = "Done", History New Value Start = 1/20/2025
Any ideas how to proceed here? I've tried a few measures that will only give me the correct number of tickets assigned on that given day, not including tickets that are still under their assignment at that time.
I am using PowerBI Jira Connector app through Jira with OData feed.
example data, thank you!
| Key | History Field | History New Value | History New Value Start | History New Value End |
| TEST-100 | assignee | bob | 1/15/2025 | |
| TEST-101 | assignee | ashley | 1/16/2025 | |
| TEST-102 | assignee | bob | 1/17/2025 | |
| TEST-100 | status | done | 1/20/2025 | |
| TEST-104 | assignee | bob | 1/25/2025 | 1/30/2025 |
| TEST-104 | assignee | ashley | 1/30/2025 | |
| TEST-104 | status | done | 2/10/2025 | |
| TEST-107 | assignee | bob | 2/15/2025 |
Hi BSands ,
As we haven’t heard back from you, we will go ahead and close this ticket for now.
If you need any further assistance, please don’t hesitate to raise a new ticket, we’re always happy to help.
If the issue still persists, I’d recommend raising a support ticket with Microsoft. The support team can look into the backend and provide more in-depth assistance tailored to your environment.
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket
Our sincere apologies if there was any inconvenience caused.
Regards,
B Manikanteswara Reddy
13 Replies
- Akash_Varuna
Super User
Hi BSands For this, you can do this by creating a Date Table to track ticket assignments over time. Then, use a DAX measure to count active tickets where the start date is on or before the current date and the end date is after or blank. Add another measure to include tickets marked as "done" by filtering on the status and start date then combine these measures to visualize the total tickets (active and done) over time in a line chart.
I tried this and got a visual that showed the correct number of tickets that were added to the persons assignment on that day, IE. bob was assigned 2 tickets on 2/15, then 2 on 2/17, bringing his total to 4, but the graph would show 2 on 2/15, 0 on 2/16, then 2 on 2/17. It doesn't keep the historical value. Do I need to create a table with the historical values over time?
Hi BSands ,
Since you are close to the solution, you can try the approach that suggested by @Akash_varuna only. The issue is that your current setup only shows tickets assigned on a specific day, but it's not tracking how many tickets are actively assigned to someone like Bob over time.
To show the full history of assignments (like a running total of what Bob has assigned on each day), you no need to create a new historical table, instead, you can create a DAX measure that checks if a ticket was still assigned on each date.
Create a normalized date column in your History_Status table if History New Value Start contains timestamps:
StartDateOnly = DATE(YEAR([History New Value Start]), MONTH([History New Value Start]), DAY([History New Value Start]))
You can do the same for History New Value End column as well.
Then create the DAX as below:
Tickets Assigned to Bob =
VAR SelectedDate = SELECTEDVALUE('Date'[Date])
RETURN
CALCULATE(
COUNTROWS(
FILTER(
'History_Status',
'History_Status'[History Field] = "assignee" &&
'History_Status'[History New Value] = "bob" &&
'History_Status'[StartDateOnly] <= SelectedDate &&
(
ISBLANK('History_Status'[EndDateOnly]) ||
'History_Status'[EndDateOnly] >= SelectedDate
) &&
NOT (
'History_Status'[Key] IN
CALCULATETABLE(
VALUES('History_Status'[Key]),
'History_Status'[History Field] = "status",
'History_Status'[History New Value] = "done",
'History_Status'[StartDateOnly] <= SelectedDate
)
)
)
)
)
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi BSands ,
Thank you for reaching out to Microsoft Farbric Community Forum.
Akash_Varuna Thank you for your quick inputs.
BSands , As Akash_Varuna mentioned, the first step is to create a Calendar table, which allows analyzing ticket assignments on a day-by-day basis as below:
Calendar =
CALENDAR(
MIN('History_Status'[History New Value Start]),
MAX('History_Status'[History New Value Start])
)Then create the below DAX Measure to Count Assigned Tickets (e.g., for "bob"):
Tickets Assigned to Bob =
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
RETURN
CALCULATE(
COUNTROWS(FILTER('History_Status',
'History_Status'[History Field] = "assignee" &&
'History_Status'[History New Value] = "bob" &&
'History_Status'[History New Value Start] <= SelectedDate &&
(
ISBLANK('History_Status'[History New Value End]) ||
'History_Status'[History New Value End] >= SelectedDate
) &&
NOT (
'History_Status'[Key] IN
CALCULATETABLE(
VALUES('History_Status'[Key]),
'History_Status'[History Field] = "status",
'History_Status'[History New Value] = "done",
'History_Status'[History New Value Start] <= SelectedDate
)
)
))
)If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hello,
Thank you, I have a date table with date/time format, A true/false column for IsBusinessDay and BusinessDayIndex. I tried the measure you gave and it comes up blank when I try to create a visual with it. The date/time for historynewvaluestart has an exact time, where my date table all has 12:00:00am so I created another column in the history_status table converting them all to 12:00:00am, but still nothing comes up.
