Calculating total no of tickets raised and closed in a particular month

[MayurGaikwad]

Hi,

Recently, I've started using Power Bi to build dashboards using Jira software data. My task is to calculate total no of calls getting created and resolved in a month.

below are the only fields available from Jira dump.

1. Created Date

2. Resolved Date

3. Key ID (Issue ID)

how can i build below table structure

Month | Year | Created | Resolved

January | 2023 | 15 | 10

[MayurGaikwad]

@jdbuchanan71 Thank you so much for such a detailed solution. Let me try this. 🙂

[MayurGaikwad]

Hi, @jdbuchanan71, I checked the attached solution but my tickets table also contains 'null' values in Resolved_Dates. Hence its coming like this. Any fix for this.

[MayurGaikwad]

Hi @jdbuchanan71, its resolved. Due to data type mistmatch the relationship was not able to fetch details from date column in tickets table. Its working now. Thank once again for the detailed description.

[jdbuchanan71 · Accepted solution]

You will need to add a Dates table to your model, and link it to your Tickets on both the [Created Date] and the [Resolved Date].  Only the [Created Date] relationship will be active but, we can use the link to [Resolved Date] in a measure.

You can create a Dates table with this DAX code.  

Dates = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Month Year", EOMONTH ( [Date], 0 )
)

Link the Dates table to the Tickets table, first on 'Dates'[Date] > 'Tickets'[Created Date], then on 'Dates'[Date] > 'Tickets'[Resolved Date].  The Date > Resolved date will be inactive (dotted line).

Then go to the Model view and select the [Month Year] column and apply a custom format of "mmm-yyyy":

Next, two measures to calculate the ticket counts.

Created Tickets = COUNTROWS ( Tickets )

Resolved Tickets = CALCULATE ( COUNTROWS ( Tickets ), USERELATIONSHIP ( Tickets[Resoved Date], Dates[Date] ) )

You can see where the activate the relationship from [Date] > [Resolved Date] in the second measure.

Now we can pull the Dates[Month Year] into a table and add our two measures.

I have attached my sample file for to look at.