calculating historical data for jira

[dhoffmann]

Hello everyone,

I'm trying to calculate the number of unresolved tickets (where the column status has no entry, so the status isn't closed, resolved or anything else) for every given Monday in my Jira project. For this, I have the following tables imported into my Power BI:

-A table called "Issues" that contains the columns: ISSUE_KEY (a unique key to identify the ticket), Creation Date (the date on which the ticket was created), ISSUE_STATUS_NAME (the status of the ticket at this moment in time), and other irrelevant data for this problem.

-A table called "History" that contains the columns: ISSUE_KEY (again the unique ticket identifier), FIELD (the name of an updated field in the ticket, only the field ISSUE_STATUS_NAME is relevant here), CREATED (the date on which the FIELD was updated), NEW_VALUE_FORMATTED (the updated value in the FIELD), and more unneeded data.

-A table called "Mondays" with a column "mondays" that contains the dates for which I want to calculate the number of unresolved tickets at that time.

Using this data, it should be possible to dynamically calculate the status for every ticket for every given Monday, count them, and then add the ticket amount as a new column to my "Mondays" table. However, I cannot find the right way to implement that in Power BI. Hoping to hear about your solutions here 🙂

[Anonymous]

Hi @dhoffmann ,

Based on your description, the following data model can be created.
Create a calculated column in the History table to determine if tickets are unresolved at each update date.

IsUnresolved = 
IF (
    History[FIELD] = "ISSUE_STATUS_NAME"
        && 'History'[NEW_VALUE_FORMATTED] <> "Closed"
        && 'History'[NEW_VALUE_FORMATTED] <> "Resolved",
    1,
    0
)

Then create a MEASURE to get the count of unresolved tickets for each Monday.

UnresolvedTicketsCount_ = 
CALCULATE(
    COUNTROWS('History'),
    FILTER(
        'History',
        'History'[IsUnresolved] = 1 &&
        'History'[CREATED] <= MAX('Mondays'[mondays]) &&
        'History'[ISSUE_KEY] IN VALUES('Issues'[ISSUE_KEY])
    )
)

Best Regards,
Adamk Kong

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

[dhoffmann]

Hi, and thank you for your response @Anonymous . I think I understand what you are trying to do here.

I had to update your code a bit to match my case (and because the table/column names I provided were incorrect in some cases). Here is my updated code now:

IsUnresolved =
IF (
'JSM | Histories'[FIELD] = "resolution"
&& ISBLANK('JSM | Histories'[NEW_VALUE_FORMATTED]),
1,
0
)

and:

UnresolvedTicketsCount_ =
CALCULATE(
COUNTROWS('JSM | Histories'),
FILTER(
'JSM | Histories',
'JSM | Histories'[IsUnresolved] = 1 &&
'JSM | Histories'[CREATED] <= MAX('Mondays'[Mondays]) &&
'JSM | Histories'[ISSUE_KEY] IN VALUES('JSM | Issues'[ISSUE_KEY])
)
)

This seems to work until a certain point, as I get a calculated number when I drop the UnresolvedTicketCount_ into a table visual. However, when I add the Mondays column to the table, it just shows blank. Do you have any idea why that is? Also, from what I understand, this solution only pays attention to tickets that had their resolution changed back to BLANK (unresolved) in the past and ignores tickets with resolution BLANK at the moment (tickets that haven't had their status changed yet). Is that correct?

[Anonymous]

Hi @dhoffmann ,

Please provide screenshot information and describe it.

Best Regards,
Adamk Kong

[dhoffmann]

sure thing, thank you for taking your time.

first, here is a screeshot of my Histories table, where i calculated the IsUnresolved column:

next, my issues table, relevant columns highlighted:

and last, the visual with the mondays and UnresolvedTicketCount_ columns:

please let me know if you need anything more, best regards!