Solved: Help needed with Recursive / Iterative Calculation

asah · ‎02-05-2020

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

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

11115095

P4

Solved

Achieved

Incident

49

0

P12

2019-20

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

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

0

P12

2019-20

FALSE

03-Feb-20

04-Feb-20

11115103

P4

Solved

Breached (Fulfilled)

Incident

50

0

P12

2019-20

TRUE

FALSE

04-Feb-20

05-Feb-20

11115104

P4

Solved

Breached (Fulfilled)

Incident

50

0

P12

2019-20

TRUE

FALSE

04-Feb-20

05-Feb-20

11115105

P4

Solved

Breached (Fulfilled)

Incident

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

11115107

P2

Solved

Achieved

Incident

50

0

P12

2019-20

FALSE

04-Feb-20

05-Feb-20

11115108

P3

Solved

Breached (Fulfilled)

Task

50

0

P12

2019-20

TRUE

FALSE

04-Feb-20

11115109

P3

Solved

Achieved

Task

50

0

P12

2019-20

FALSE

04-Feb-20

11115110

P3

Solved

Achieved

Task

50

0

P12

2019-20

FALSE

04-Feb-20

11115111

P3

Solved

Achieved

Task

50

0

P12

2019-20

FALSE

04-Feb-20

11115112

P3

Solved

Achieved

Task

50

0

P12

2019-20

FALSE

04-Feb-20

11115113

P3

Solved

Achieved

Task

50

0

P12

2019-20

FALSE

04-Feb-20

05-Feb-20

11115114

P3

Solved

Achieved

Task

50

0

P12

2019-20

FALSE

03-Feb-20

11115115

P3

Open

Breached (Active)

Incident

50

1

P12

2019-20

TRUE

asah · ‎02-17-2020

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.

View solution in original post

tkirilov · ‎02-05-2020

Hi @asah ,

Perhaps the following measure:

OpenTicketCount = Calculate(Distinctcount([Ticket Id]), [IsBreached] = "True")

And if you slice that by date in a table visual for example, it should give you the desired result. That's if I've fully understood your requirement of course.

ImkeF · ‎02-05-2020

Hi @asah

not sure about your requirement here. With regards to the first row: Do you want to count this item as open at the date of 31. January of example? So basically create a history of open/closed status?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

asah · ‎02-05-2020

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

ImkeF · ‎02-05-2020

Hi @asah ,

so you're looking for a column on the existing table or a measure in your model?

Am I assuming correctly, that all this has to be applied on Ticket-ID-Level (as row 4 starts again with 29th Jan..)?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

asah · ‎02-05-2020

Hi Imke,

I am hoping to produce a column as I'd like to display the values as a part of a matrix with other columns. The ticket IDs are incremental in nature so ideally should be by date created and then the ticket id's as multiple tickets can be created on the same day. My sample data was not sorted so may be causing the confusion.

ImkeF · ‎02-05-2020

Thanks @asah for clarification.

Could you please provide new correct sample data and also display the desired result?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

asah · ‎02-08-2020

Hi Imke,

Please see the below sample data:

Date (Ticket Created)

Date (Ticket Solved)

Ticket Id

Ticket Priority

Ticket Status

SLA Metric Status

Ticket Type

WeekTicketCreate

WeekTicketSolve

Period

Year

IsSLABreach

Backlog out of SLA

Comments

29-Jan-20

30-Jan-20

11111111

P4

Solved

Breached (Fulfilled)

Incident

49

P12

2019-20

TRUE

1

#11111111

30-Jan-20

03-Feb-20

11111112

P3

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

1

#11111112

30-Jan-20

03-Feb-20

11111113

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

2

#11111112, #11111113

30-Jan-20

03-Feb-20

11111114

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

3

#11111112, #11111113, #11111114

31-Jan-20

03-Feb-20

11111115

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

4

#11111112, #11111113, #11111114, #11111115

31-Jan-20

11111116

P4

Solved

Achieved

Incident

49

P12

2019-20

FALSE

4

#11111112, #11111113, #11111114, #11111115

31-Jan-20

03-Feb-20

11111117

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

5

#11111112, #11111113, #11111114, #11111115, #11111117

31-Jan-20

04-Feb-20

11111118

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

6

#11111112, #11111113, #11111114, #11111115, #11111117, #11111118

01-Feb-20

05-Feb-20

11111119

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

7

#11111112, #11111113, #11111114, #11111115, #11111117, #11111118, #11111119

02-Feb-20

11111120

P4

Open

Breached (Active)

Incident

49

P12

2019-20

TRUE

8

#11111112, #11111113, #11111114, #11111115, #11111117, #11111118, #11111119, #11111120

02-Feb-20

04-Feb-20

11111121

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

9

#11111112, #11111113, #11111114, #11111115, #11111117, #11111118, #11111119, #11111120, #11111121

02-Feb-20

04-Feb-20

11111122

P4

Solved

Breached (Fulfilled)

Incident

49

50

P12

2019-20

TRUE

10

#11111112, #11111113, #11111114, #11111115, #11111117, #11111118, #11111119, #11111120, #11111121, ##11111122

03-Feb-20

04-Feb-20

11111123

P4

Solved

Achieved

Incident

50

P12

2019-20

FALSE

5

#11111118, #11111119, #11111120, #11111121, ##11111122

03-Feb-20

04-Feb-20

11111124

P4

Solved

Breached (Fulfilled)

Incident

50

P12

2019-20

TRUE

6

#11111118, #11111119, #11111120, #11111121, #11111122, #11111124

03-Feb-20

11111125

P3

Open

Breached (Active)

Incident

50

P12

2019-20

TRUE

7

#11111118, #11111119, #11111120, #11111121, #11111122, #11111124, #11111125

04-Feb-20

11111126

P3

Open

Active

Incident

50

P12

2019-20

FALSE

3

#11111119, #11111120, #11111125

04-Feb-20

11111127

P3

Open

Active

Incident

50

P12

2019-20

FALSE

3

#11111119, #11111120, #11111125

04-Feb-20

05-Feb-20

11111128

P4

Solved

Breached (Fulfilled)

Incident

50

P12

2019-20

TRUE

4

#11111119, #11111120, #11111125, #11111128

04-Feb-20

05-Feb-20

11111129

P4

Solved

Breached (Fulfilled)

Incident

50

P12

2019-20

TRUE

5

#11111119, #11111120, #11111125, #11111128, #11111129

04-Feb-20

11111130

P4

New

Active

Incident

50

P12

2019-20

FALSE

5

#11111119, #11111120, #11111125, #11111128, #11111129

04-Feb-20

11111131

P2

Solved

Achieved

Incident

50

P12

2019-20

FALSE

5

#11111119, #11111120, #11111125, #11111128, #11111129

04-Feb-20

05-Feb-20

11111132

P3

Solved

Breached (Fulfilled)

Task

50

P12

2019-20

TRUE

04-Feb-20

11111133

P3

Solved

Achieved

Task

50

P12

2019-20

FALSE

04-Feb-20

11111134

P3

Solved

Achieved

Task

50

P12

2019-20

FALSE

04-Feb-20

11111135

P3

Solved

Achieved

Task

50

P12

2019-20

FALSE

04-Feb-20

11111136

P3

Solved

Achieved

Task

50

P12

2019-20

FALSE

04-Feb-20

11111137

P3

Solved

Achieved

Task

50

P12

2019-20

FALSE

04-Feb-20

05-Feb-20

11111138

P3

Solved

Achieved

Task

50

P12

2019-20

FALSE

I have manually calculated the backlog of open tickets where IsSLABreach = True for each day based on the preceeding dates and have mentioned the ticket nos. to support the count. What I am looking for is a dynamic count as the one mentioned in the example, fetch only the last value for each date as that will be accurate and the ability to customize the formula based on ticket type and ticket priority.