Calculating active issues per month

[Anonymous]

I have a table with list of issues having columns created date , closed date , status(open/closed) as below

ID Created Date             Closed Date                  Status
1 10/9/2021 8:11:22 PM 12/2/2021 9:32:00 PM Closed
2 1/9/2021 8:11:22 PM                                        Open

I want to calculate the the list of active issues per month. The list of active issues for a month would be the number of active issues for previous months + number of new issues created for that month - the number of issues closed in that month.
The number of new issues created for that month is the todal number of issues created and uses the created date column
The number of issues closed for that mnth would be the total number of issues closed in that month and uses the closed date column.
Below is a sample data of how 'Active would be calculated

           Jan-22 Feb-22 Mar-22 Apr-22 May-22
New     0          4          30          2          5
Closed 0          0           0           2          3
Active  0          4          34          34        36

Can someone help me how to derive the active data and display in a line chart?

[Mikelytics · Accepted solution]

Hi @Anonymous,

Please find below a solution approach:

Base table:

Data model:

active relationship between DimDate[Date] and Ticket[CreatedDate]

inactive relationship between DimDate[Date] and Ticket[ClosedDate]

I calculated 4 measures in total:

01 New  Tickets = 
COUNTROWS(Tickets)

02 Total Opened Tickets = 

var var_ReferencePeriod = MAX('Dim Date'[Date])

var var_Calculation =
    CALCULATE(
        COUNTROWS(Tickets),
        ALL('Dim Date'),
        Tickets[Created Date] <= var_ReferencePeriod
    )


RETURN

var_Calculation

03 Total Closed Tickets = 

var var_ReferencePeriod = MAX('Dim Date'[Date])

var var_Calculation =
    CALCULATE(
        COUNTROWS(Tickets),
        ALL('Dim Date'),
        Tickets[Closed Date] <= var_ReferencePeriod,
        NOT ISBLANK(Tickets[Closed Date]),
        USERELATIONSHIP('Dim Date'[Date],Tickets[Closed Date])
    )


RETURN

var_Calculation 

04 Active Tickets = 
[02 Total Opened Tickets] - [03 Total Closed Tickets]

in the end the trick is to calculate all the amount of historically created tickets - historically closed tickets (based on reference date). This is how you get active tickets to reference date

Result in a matrix visual:

to show the measures in rows please use the formatting functionaliry in the format pane for the matrix

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[Anonymous]

@Mikelytics . Thank you , your trick for the calculation of active count has worked. 

However I am unable to apply any visual filters to the charts created , any idea what could be the reason or how I can apply the filters. For eg. I would like to filter the issues based on the criticality and want to display only the Critical issues in the chart. 

[v-xiaosun-msft]

Hi @Anonymous ,

According to your description, I made a sample and here is my solution.

Create a DATE table.

DATE = CALENDAR(DATE(2022,1,1),DATE(2022,6,30))

Create two columns to return the month of your "Created Date" and "Closed Date".

month of created = MONTH('test'[Crated Date])

month of closed = MONTH('test'[Closed Date])

Then create two columns to return the "New" and "Closed".

new = CALCULATE(COUNT('test'[month of created]),FILTER('test','test'[month of created]=EARLIER(test[month of created])))

closed = CALCULATE(COUNT('test'[month of closed]),FILTER('test','test'[month of closed]=EARLIER(test[month of closed])&&'test'[Closed Date]<>BLANK()))

Put them into a line chart.

Best Regards,
Community Support Team _ xiaosun

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

[Anonymous]

Thank you for the response.

I have tried to recreate what you have suggested for a sample data , but I am not getting the desired output.
Please find the attached sample pbix file which I have tried .

https://file.io/0Qy729iKjbsW
Will this solution work if there are months from different years , say 2021 and 2022?
Also what I needed to calculate was the active count for each month which again would be would be the number of active issues for previous months + number of new issues created for that month - the number of issues closed in that month.
Can you please help me to calculate this.
I have attached the desired output that I am looking for. This data corresponds to the sample pbix file attached.