Calculating case handling time for open and closed cases back in time

[Anonymous]

Hi there,

I am having difficulty making a calculation for our historic case handling times (i.e. how long it takes for us, on average, to work on a case). I need to make these calculations for both open and closed cases. I believe that my current calculation method works well for calculating the current status of case handling times, however the problem is with the historical times. I cannot figure out a way to get the DAX to see that a case, at some point, changes from open to closed.

I cannot share the data I work with, but I have given an example of what it looks like below. 'Open/Closed' expresses the current status of the case. The 'Date' column is linked to a calendar table.

CaseNr	Attribute	Date	Open/Closed
10001	Received	01-01-2022	Closed
10001	Created	01-01-2022	Closed
10001	Closed	29-03-2022	Closed
10008	Received	01-02-2022	Open
10008	Created	02-02-2022	Open
10010	Received	03-02-2022	Closed
10010	Created	03-02-2022	Closed
10010	Closed	09-02-2022	Closed
10012	Received	12-12-2021	Closed
10012	Created	13-12-2021	Closed
10012	Closed	04-03-2022	Closed
10015	Received	04-11-2021	Open
10015	Created	04-11-2021	Open

The calculation of time for open cases is done through two measures:

Open cases setup = 
        var selected_date = MAX(Date1[Date])
        var Tabel1 = ADDCOLUMNS(

                                   VALUES('Historic Cases'[CaseNr]),

                                    "@received_date", CALCULATE(MIN('Historic Cases'[Date]), 'Historic Cases'[Attribute] = "Received" || 'Historic Cases'[Attribute] = "Created") )

        var Tabel2 = ADDCOLUMNS(

                                 Tabel1,

                                 "@casehandlingtime", DATEDIFF( [@received_date], selected_date, DAY)

return
calculate(AVERAGEX(tabel2, [@casehandlingtime]))

This then feeds into the following measure:

Historic case handling time = 

     VAR currentdate = MAX(Date1[Date])

     CALCULATE( 'Historic Cases'[Open cases setup], Date1[Date] <= currentdate,
     'Historic Cases'[Open/Closed] = "Open")

The red part at the end here is where I believe my problem is. This column is 'static' - it tells me that the case is open/closed now, but it can't tell we what the case was two months ago. The reason that this is a problem is that a case might be closed now, but was open two months ago. I need to show the development of case handling time month-month on a line graph. The current DAX only includes cases that are still open. The case handling time for, e.g. february, will be wrong, because cases that were open in February, but have since been closed will not be included in the calculation for the months before February.

Is there something that I can add to the first measure to make it recognise the changing status (open/closed) of a case?

I hope this explanation makes sense, any help would be greatly appreciated 🙂

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('Date'[Date])
var _1=CALCULATE(MAX('Table'[Open/Closed]),FILTER(ALL('Table'),'Table'[CaseNr]=MAX('Table'[CaseNr])&& 'Table'[Date]<=_select))
var _last=IF(
    MAX('Table'[Date])<=_select && _1="Closed",MAXX(FILTER( ALLSELECTED('Table'),'Table'[Date]<=_select&&'Table'[CaseNr]=MAX('Table'[CaseNr])) ,'Table'[Date]),BLANK())
return
_last

2. Result:

Best Regards,

Liu Yang

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

[ManguilibeKAO]

Hi,

In order to solve your issue, I need some clarification on the meaning of your data: could the same case be closed multiple times or be opened multiple times?  For example, in your data, the case 10008 is open two times. the case 100010 is closed two times.

Regards.