Month on month count of carry forward tickets

[Sunil2020]

Hi

I have recently started learning Power BI. This is one scenario which i'm struggling to find a solution.

• I have a table with details of tickets raised (I have used dummy values in this table)
• New Support Tickets are raised by users every month
• Some tickets are cancelled by users
• Some tickets are resolved in the same month
• Some tickets get carried forward to next month OR even carried forward for more than 2-3 months

For each month I want to get count of Tickets carried from previous month(s) i.e. Need count of all open tickets raised in the past months which are still open OR which have been closed in the current month OR future months. 

Data Table Format

Ticket ID	Request Date	Ticket Raised BY	Ticket Status	Closed Date
101	25-Mar-20	Jackie	Open	null
102	25-Mar-20	Sam	Resolved	2-Apr-20
103	3-Apr-20	Jackie	Cancelled	5-Apr-20
104	5-Apr-20	Jackie	Resolved	10-May-20
105	15-Apr-20	Sam	Open	null
106	20-May-20	Jackie	Resolved	25-May-20
107	24-May-20	Jackie	Open	null
108	24-May-20	Sam	Resolved	3-Jun-20
109	28-May-20	Sam	Resolved	15-Jun-20
110	30-May-20	Jackie	Open	null
111	3-Jun-20	Sam	Open	null

Expected Output:

Month	C/F Count
Apr-2020	2
May-2020	3
Jun-2020	6

Should I create a separate summary table and include a formula-based custom column OR this can be achieved by using a measure?

Appreciate the help. Thank you in advance.

[Almercie]

I ended up solving this via a calculated column for my own reporting.

Carry Over Tickets = 

VAR startmonth =
    MONTH ( Incident[Created Date] )

VAR endmonth =
    IF (
        ISBLANK ( Incident[Resolved Date] ),
        MONTH ( TODAY () ),
        MONTH ( Incident[Resolved Date] )
    )

RETURN
    IF ( startmonth = endmonth, 0, 1 )

[mahoneypat · Accepted solution]

Here is an expression that returns your desired results.  It assumes you have a Date table with no relationship to your Tickets table, and you've used a YearMonth column from that Date table in your visual. If you do have a relationship, you can all ALL('Date'[Date]) to the CALCULATE().

Carryover Tickets =
VAR __mindate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Tickets ),
        Tickets[Request Date] < __mindate,
        OR ( Tickets[Closed Date] >= __mindate, ISBLANK ( Tickets[Closed Date] ) )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[HarshalK]

Hello Pat,
In my case there are three service line and need to create report like given below:

 I have created new table for this calculation as given below.

I can see there were mismatch in backlog calculation while trying to use your DAX formula.

e.g: For Service 'A'  there is actual start of service "A" is from Mar-16 so previous value showing 0 for all columns.

       As per DAX formula for Service "A" for Mar-16

       Inflow =6
       Closed = 3 then Backlog should be : Carry_Fwd (0)+6-3 = 3  but DAX formulation value showing 4.

Same for Apr-16: if logically Backlog considering 4 as Carry_Fwd of last month 4+9-3 = 10 but it showing 9.

I have made little bit change and used below DAX formula:

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

Backlog =

var _ser = NEW_MOR[Service]

var _date = NEW_MOR[End_Date_Month]

var Carr_Value = CALCULATE (

COUNTROWS ( Sheet1 ),

Sheet1[Service]= _ser,

Sheet1[Opened] <= _date, OR(Sheet1[Closed] > _date, ISBLANK ( Sheet1[Closed]) ) )

RETURN

IF(ISBLANK(Carr_Value),0,Carr_Value)

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

Could you please help me Backlog formula so I can create another table of Carr Fwd. By considering old month Backlog = next Month Carry Fwd?

I'm new in Power Bi and still learning for better understing.

Kind Regards,

Harshal Kulkarni 

[Anonymous]

Pat,

I have a similar problem I am trying to solve. Your recommendation worked with the Date table that doesn't have a relationship but I need to use a date table that has a relationship since I need to chart (line) of the Carryforward on the chart that also shows the number of new and closed tickets (same timeline). You said that in such a case just to add ALL('Date'[Date]) to CALCULATE(). I tried different ways but cannot get it right. Can you please clarify how would i do it in that case?.. thanks ! 

[Ashish_Mathur]

Hi,

Share some data and show the expected result in a simple table format.  Once we get the correct result in a Table, we can always switch to any other visual.

[Anonymous]

It is same input data as in the original post and same expected results. The difference is that I have to use Date table that has active relationship to Request Date column and inactive relationship to Closed date. 

Here is the example of the visual. I (already) have new and closed tickets, but need to also have a line that represents total open in that month (some are from that month, some are carry-overs from previous months - just same as in the original post). 

thank you! 

[Ashish_Mathur]

I cannot undertnd the expected result.  For June 2020, why should the answer be 6?

[Anonymous]

Ashish,

thank you.. nvm... I finally was able to find a solution from another community post that worked. thank you for looking into this for me. 

[Sunil2020]

Thank you Pat. Your formula helped me to find the solution. Really appreciate the help.

In my model, I'm creating an extra table with one of the columns as the Report published Date (Data is refreshed on the last day of each month). In this table, I have added a few custom fields in the table including one custom field to calculate the count of carry forward tickets in the below manner.

Tickets C/F = CALCULATE(
COUNTROWS('Tickets'),
FILTER(
'Tickets',
(('Tickets'[Status] = "Open" && 'Ticket'[Request Date] < [MonthStartDate]) || ('Tickets'[Status]<> "Open" && 'Tickets'[Request Date] < [MonthStartDate] && 'Tickets'[Closed Date]>=[MonthStartDate]))
)
)

[lbendlin]

You can turn your question logic around.

Find all tickets that have NOT been closed in the month they were opened.

[Sunil2020]

That would have worked if I need to know which tickets raised in the previous month which were not closed and carried forward to current month. I also need to consider the tickets which were raised in the past months. Such tickets will be carried forward for more than 1 month.