calculate the open and closed tickets and response taken by each user from open to close

[Anonymous]

I have the data from API that cotains these columns .

id,OpenDate,Employee,Status,Close date.

These are the things which i need to find out from this.

1. Calculate the last 30 days requests
2. Calculate the last 30 days closed request
3. Calculate the Average response time to close each request
4. Calculate the open and close request per day
5. Calculate the avergae response time by emoloyee to close the request. 

Please find the sample data for the same. Could you please let me know how to calculate these open  and closed tickets based on the uniqueid and other details provided below.

i have already looked into different groups of power bi and was not able to find the exact solution for this issue. if you see the data below the id is unique for open and closed. 

i am trying to create a report similar to a devops thing. 

Id

Group

Type

OpenDate

Employee

UpdateDate

Status

ClosedDate

Quarter

Month

Day

YearOpened

YearClosed

MonthOpened

MonthClosed

MonthNumber

CreatedDates

5d64fec8-7ac4-4393-8893-08d7d4ad4421

Req 30th

Type 30th

3/30/2020 13:34

Test 1

open

Q1

March

30

2020

3

3

3/30/2020 13:34

e802fd3b-5dc3-4bc0-b6cb-08d7d4c6734f

Req 30th

Type 30th

3/30/2020 16:22

Test 2

open

Q1

March

30

2020

3

3

3/30/2020 16:22

6ce82225-55ff-4258-b6d2-08d7d4c6734f

Req 30th

Type 30th

3/30/2020 16:50

Test 3

open

Q1

March

30

2020

3

3

3/30/2020 16:50

4a46eef0-118b-4509-03d3-08d7d5347b8c

Request 31st group

Request 31st Type

3/31/2020 5:29

Test 3

open

Q1

March

31

2020

3

3

3/31/2020 5:29

c90b2fa8-a30f-462c-03d4-08d7d5347b8c

Request 31st group

Request 31st Type

3/31/2020 5:29

Test 4

open

Q1

March

31

2020

3

3

3/31/2020 5:29

8cbb050b-567e-4663-d6ae-08d7e1022950

Testing hire

Hire new

4/15/2020 5:59

Test 5

open

Q2

April

15

2020

4

4

4/15/2020 5:59

b827cd4e-ce02-4d8f-97f2-08d7e210453d

Req-16a

Req test 16

4/16/2020 14:23

Test 4

open

Q2

April

16

2020

4

4

4/16/2020 14:23

5c6ba1f4-bc0d-412b-755b-08d7e2b1758d

new group for hire & new position

for hire

4/17/2020 12:22

Test 6

open

Q2

April

17

2020

4

4

4/17/2020 12:22

d860fdd1-3043-47bc-755f-08d7e2b1758d

new group for hire & new position

without temp 2

4/17/2020 12:26

Test 7

open

Q2

April

17

2020

4

4

4/17/2020 12:26

b1bc90ad-beee-4227-f75b-08d7d230df8e

Basic

Basic

3/27/2020 9:26

Test 8

3/27/2020 9:26

closed

3/27/2020 9:26

Q1

March

27

2020

2020

3

3

3

3/27/2020 9:26

ca44903c-25f5-4e88-f9ef-08d7d2352158

Request Group 27th

Request Type 2

3/27/2020 10:08

Test 3

3/27/2020 10:08

closed

3/27/2020 10:08

Q1

March

27

2020

2020

3

3

3

3/27/2020 10:08

ddc014f2-14fb-4e66-040a-08d7d23a0016

Request Group 27th

Request Type 2

3/27/2020 10:31

Test 3

3/27/2020 10:31

closed

3/27/2020 10:31

Q1

March

27

2020

2020

3

3

3

3/27/2020 10:31

69b08ca2-d68d-4eaf-040c-08d7d23a0016

Basic

Basic

3/27/2020 10:43

Test 3

3/27/2020 10:43

closed

3/27/2020 10:43

Q1

March

27

2020

2020

3

3

3

3/27/2020 10:43

0a2fac88-c23b-4dc3-040d-08d7d23a0016

Basic

Basic

3/27/2020 10:45

Test 3

3/27/2020 10:45

closed

3/27/2020 10:45

Q1

March

27

2020

2020

3

3

3

3/27/2020 10:45

d569ad52-bb09-46af-f8db-08d7d23fadd6

Request Group 27th

Request Type 2

3/27/2020 11:12

Test 3

3/27/2020 11:12

closed

3/27/2020 11:12

Q1

March

27

2020

2020

3

3

3

3/27/2020 11:12

434ffe90-b6e7-4f0f-f8dc-08d7d23fadd6

Request Group 27th

Request Type 2

3/27/2020 11:42

Test 3

3/27/2020 11:42

closed

3/27/2020 11:42

Q1

March

27

2020

2020

3

3

3

3/27/2020 11:42

d8a063fc-8cae-420d-18bb-08d7d2cad09e

Req 28

R 28

3/28/2020 6:10

Test 3

3/28/2020 6:10

closed

3/28/2020 6:10

Q1

March

28

2020

2020

3

3

3

3/28/2020 6:10

881c9834-a3ab-455b-8872-08d7d4ad4421

Testing hire

Hire new

3/30/2020 13:31

Test 3

3/30/2020 13:31

closed

3/30/2020 13:31

Q1

March

30

2020

2020

3

3

3

3/30/2020 13:31

Thank you in advance.

@PBICommunity 

[amitchandak · Accepted solution]

@Anonymous , Seem fine. If working for you.

[amitchandak]

Please find the Article, How to use two dates using use relation:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Asn relative date slicer: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[Anonymous]

@amitchandak 

Do you mean i need to change this dax queries in this way for open tickets, close tickets.

Open tickets = CALCULATE(COUNT(tickets[Id ]),USERELATIONSHIP(tickets[Start Date],'Date'[Date]) )

closed tickets = CALCULATE(COUNT(tickets[Id ]),USERELATIONSHIP(tickets[End Date],'Date'[Date]),not(ISBLANK(tickets[End Date])))

running tickets = CALCULATE(COUNTx(FILTER(tickets,tickets[Start Date]<=max('Date'[Date]) && (ISBLANK(tickets[End Date]) || tickets[End Date]>max('Date'[Date]))),(tickets[Id ])),CROSSFILTER(tickets[Start Date],'Date'[Date],None))

Last ticket completed = 
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',DATEADD('Date'[Date],-1,MONTH)),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
Return
 CALCULATE(COUNTx(FILTER(tickets,tickets[Start Date]<=_Expression && tickets[Start Date]>=_min_date && (ISBLANK(tickets[End Date]) || tickets[End Date]>_Expression)),(tickets[Id ])),CROSSFILTER(tickets[Start Date],'Date'[Date],None))

ticket Change% = if(not(ISBLANK([Last ticket completed])),CALCULATE( (divide([Current tickets],[Last ticket completed]) -1)*100))

[amitchandak · Accepted solution]

@Anonymous , Seem fine. If working for you.

[Anonymous]

Hi @amitchandak  

i have tried the below measure to calculate the last day currenttickets + presentday current tickets in this way. it is not giving me any data 

Lastticketmp =

IF(ISFILTERED('Dates1'[Month Year]), CALCULATE([Currentticketsmp], DATEADD('Dates1'[Date],-1,MONTH)), CALCULATE([Currentticketsmp], DATEADD('Dates1'[Date],-1,YEAR)))

Currentticketsmp = CALCULATE(COUNTx(FILTER(R_Requests1,R_Requests1[CreatedDate] <= max('Dates1'[Date]) && (ISBLANK(R_Requests1[ClosedDate] ) || R_Requests1[ClosedDate] >max('Dates1'[Date]))),(R_Requests1[Id])),CROSSFILTER(R_Requests1[CreatedDate] ,'Dates1'[Date],None))

i have tried this measure also to calculate the same. but it is not giving me any data actually.

Last Period Employee = 
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
Return
 CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

while i am validating _expression i got this data Year Quarter Month Day in this way. could you please let me know if i am not entering any format or expression correctly. Please look into it.

Year Quarter Month Day
1900 Qtr 1 January 1
1900 Qtr 1 January 2
1900 Qtr 1 January 3
1900 Qtr 1 January 4

@PBICommunity 

[Anonymous · Accepted solution]

I got the Solution. 

Running balance which will give the count of balance each row.

Running Balance =

CALCULATE(

[Balanceoftickets],

FILTER(

ALLSELECTED('R_Requests1'),

'R_Requests1'[CreatedDate] <= max('R_Requests1'[CreatedDate])

)

)

Balanceoftickets = [Created Tickets] - [ Resolved Tickets]

Created Tickets = CALCULATE([Tickets], USERELATIONSHIP('Dates1'[Date], R_Requests1[CreatedDate]))

 Resolved Tickets =

CALCULATE([Tickets],

USERELATIONSHIP('Dates1'[Date], R_Requests1[ClosedDate]),

NOT(ISBLANK(R_Requests1[ClosedDate])

))

Tickets = COUNTROWS(VALUES(R_Requests1[Id]))

@amitchandak @PBICommunity 

[Anonymous]

Hi @amitchandak 

Thanks for the help. yes it worked well for me but i miss some of them in the report. could you please let me know how to achieve this items .

Current tickets  = previous day requests + Next days Open Request (excluding the closed ones)

Last 7 days open and closed requests and 30 days open and closed requests. As you can see in below on March 27th there were 17 open and 9 closed and 8 were on going on the next day the addition of 13+8 in the ongoing.

Thanks in advance .

[Greg_Deckler]

I believe this will assist. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364