Forum Discussion
Help needed with Recursive / Iterative Calculation
Hi,
I am requesting for some help in achieving the following outcome in Power BI which requires a recursive calculation. The source data has the following columns:
Date Ticket Created - The date when the ticket was created
Date Ticket Solved - The date when the ticket was set to a solved state
Ticket ID - Unique ID of the ticket
Ticket Priority - Priority assigned to the ticket
Ticket Status - New / Open / Solved / Closed
SLA Status - Achieved / Active / Breached (Fulfilled) / Breached (Active)
Ticket Type - Incident / Problem / Task / Question
WeekTicketCreate - Week Number generated from a custom lookup table referencing the date when the ticket was created
WeekTicket Solve - Week Number generated from a custom lookup table referencing the date when the ticket was solved
Open@EOW (End of Week) - Custom column comparing if WeekTicketSolve > WeekTicketCreate and assign a 1 or 0 accordingly
IsSLABreach - Custom Column to assign True if SLA Status is Breached (Fulfilled) or Breached (Active) and False if Achieved
IsOpen - Custom Column to assign True if Ticket Status is New or Open and False if Solved or Closed
Since the source data contains historical information as well, tickets in the past may well be reflecting in a solved / closed status but I am keen to see at a given day, how many tickets raised previously or on the same day were open and out of SLA.
Requirement - For each date in the table, I am looking at generating a cumulative count of open tickets (Incident & Problems) where IsSLABreach = True for the previous or same dates.
| Date (Ticket Created) | Date (Ticket Solved) | Ticket Id | Ticket Priority | Ticket Status | SLA Metric Status | Ticket Type | WeekTicketCreate | WeekTicketSolve | Open@EOW | Period | Year | IsSLABreach | IsOpen |
| 30-Jan-20 | 03-Feb-20 | 11115088 | P3 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 04-Feb-20 | 11115089 | P3 | Open | Active | Incident | 50 | 0 | P12 | 2019-20 | FALSE | TRUE | ||
| 04-Feb-20 | 11115090 | P3 | Open | Active | Incident | 50 | 0 | P12 | 2019-20 | FALSE | TRUE | ||
| 29-Jan-20 | 30-Jan-20 | 11115091 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 49 | 0 | P12 | 2019-20 | TRUE | FALSE |
| 30-Jan-20 | 03-Feb-20 | 11115092 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 30-Jan-20 | 03-Feb-20 | 11115093 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 31-Jan-20 | 03-Feb-20 | 11115094 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 31-Jan-20 | 31-Jan-20 | 11115095 | P4 | Solved | Achieved | Incident | 49 | 49 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 31-Jan-20 | 03-Feb-20 | 11115096 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 31-Jan-20 | 04-Feb-20 | 11115097 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 01-Feb-20 | 05-Feb-20 | 11115098 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 02-Feb-20 | 11115099 | P4 | Open | Breached (Active) | Incident | 49 | 1 | P12 | 2019-20 | TRUE | TRUE | ||
| 02-Feb-20 | 04-Feb-20 | 11115100 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 02-Feb-20 | 04-Feb-20 | 11115101 | P4 | Solved | Breached (Fulfilled) | Incident | 49 | 50 | 1 | P12 | 2019-20 | TRUE | FALSE |
| 03-Feb-20 | 04-Feb-20 | 11115102 | P4 | Solved | Achieved | Incident | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 03-Feb-20 | 04-Feb-20 | 11115103 | P4 | Solved | Breached (Fulfilled) | Incident | 50 | 50 | 0 | P12 | 2019-20 | TRUE | FALSE |
| 04-Feb-20 | 05-Feb-20 | 11115104 | P4 | Solved | Breached (Fulfilled) | Incident | 50 | 50 | 0 | P12 | 2019-20 | TRUE | FALSE |
| 04-Feb-20 | 05-Feb-20 | 11115105 | P4 | Solved | Breached (Fulfilled) | Incident | 50 | 50 | 0 | P12 | 2019-20 | TRUE | FALSE |
| 04-Feb-20 | 11115106 | P4 | New | Active | Incident | 50 | 0 | P12 | 2019-20 | FALSE | TRUE | ||
| 04-Feb-20 | 04-Feb-20 | 11115107 | P2 | Solved | Achieved | Incident | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 04-Feb-20 | 05-Feb-20 | 11115108 | P3 | Solved | Breached (Fulfilled) | Task | 50 | 50 | 0 | P12 | 2019-20 | TRUE | FALSE |
| 04-Feb-20 | 04-Feb-20 | 11115109 | P3 | Solved | Achieved | Task | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 04-Feb-20 | 04-Feb-20 | 11115110 | P3 | Solved | Achieved | Task | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 04-Feb-20 | 04-Feb-20 | 11115111 | P3 | Solved | Achieved | Task | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 04-Feb-20 | 04-Feb-20 | 11115112 | P3 | Solved | Achieved | Task | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 04-Feb-20 | 04-Feb-20 | 11115113 | P3 | Solved | Achieved | Task | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 04-Feb-20 | 05-Feb-20 | 11115114 | P3 | Solved | Achieved | Task | 50 | 50 | 0 | P12 | 2019-20 | FALSE | FALSE |
| 03-Feb-20 | 11115115 | P3 | Open | Breached (Active) | Incident | 50 | 1 | P12 | 2019-20 | TRUE | TRUE |
Apologies for the delayed response, I have found the solution to my query here:
https://community.powerbi.com/t5/Desktop/Cumulative-backlog-to-date/td-p/35469
Thanks for your time and I really appreciate you looking into this for me.
9 Replies
Hi ImkeF
Thanks for looking into this. Apologies, I noticed that I did not sort the sample data before uploading. It should be sorted first on the created date and then the ticket id. Regardless of that, this is what I am trying to achieve in a column:
1) Considering that ticket created on 30-Jan is the first ticket in the source data, the backlog of open tickets out of SLA will be 0
2) 2nd row suggests that a ticket has been created on 4-Feb, however the previous ticket has been closed on 3-Feb, so the backlog of open tickets out of SLA remain 0.
3) Again, there is another ticket opened on 4-Feb and the previous ticket still remains open and IsSLABreach = False, so the backlog still remains 0. However, if the IsSLABreach had been True, the value would have been 1.
So on and so forth. Please let me know if I am making sense or I will try and explain differently. Any help is greatly appreciated.
Cheers,
Anirudh
