Cumulative measure – the open helpdesk request use case

[osinquinvdm]

 I have a weird cumulative measure to create and I don’t know how to tackle it.

I guess people working with helpdesk data might have similar use cases because I’m basically trying to know how many support requests were open at any given time.

My data gives me one record per Request:

• When the request was open: [open_date]
• What the current status is (open or closed): [status]
• The time when that current status was set : [status_date]

How to determine open requests on date 😧

For currently open requests ([status]=”open”)

• They were open on date D if [open_date]<= D
• They were not open yet if  [open_date]> D

For currently closed requests ([status]=”closed”)

• They were open on date D if [status_date]> D
• They were not open any more is  [status_date]<= D

So I am basically try to do a running count of [RequestID] where

([status]=”open AND [open_date]<= D)

OR

([status]=”closed” AND  [status_date]> D)

NB: I have a time dimension ‘Dates’  in the project which is linked to the  [open_date] dimension

Something like:

CumulativeRequestCount =
CALCULATE (
    COUNT ('Requests'[RequestID] ),
    FILTER (All ('Dates'[Date]),
OR (
AND ('Requests'[status]="open" , 'Requests'[open_date]<= 'Dates'[Date],
AND ('Requests'[status]="closed" ,  'Requests'[status_date]> 'Dates'[Date]
))

Or (depending which style you like):

CumulativeRequestCount =
CALCULATE (
    COUNT ('Requests'[RequestID] ),
    FILTER ('Dates'[Date]),
('Requests'[status]="open" && 'Requests'[open_date]<='Dates'[Date])
||
('Requests'[status]="closed" &&  'Requests'[status_date]>'Dates'[Date])
))

But obviously this does not work.

What I am struggling with are the nested filteing conditions and the handling of the time dimension

[danrmcallister]

@osinquinvdm How about adding a calculated column to your date table like this?

CumulativeRequestCount = Calculate(
	Countrows(Table1),
	Filter(Requests, Requests[open_date] <= LASTDATE(DateTable[Date]) 
		&& 
		Requests[close_date] >= FirstDate(DateTable[Date])))

Why are you trying to add the evaluation of if the case is still open or not based on the status?  Shouldn't that be determined via the open/close dates?  If you have a screenshot of a reason to include that it'd be helpful.  The only thing I can think of is if you wanted to have a count for all currently open cases.

Dan

[osinquinvdm · Accepted solution]

You are right, @danrmcallister I confused you when I put close_date in the code when I should have put status_date. Because as I mentioned the “end_date” I have is not a close date, it’s the date associated with the last known status, which can be “open”.

[Well, in reality there are several intermediary statuses but this is a simplified version of the data model.]

Which is the reason why I can’t strictly rely on the dates.

Because a given date can be past the status date but the request still show a open status.

For clarity I created a sample project with a basic dataset that illustrates what the source data could look like and what the expected result would be.

I followed your lead and got it to work with the following formula

Cumulative Open Requests Count = CALCULATE(
            COUNT('status-dataset'[RequestID]);
            FILTER(
                        'status-dataset';
                        ('status-dataset'[open_date] <= MAX('Dates'[date]))      && (('status-dataset'[status]="open")
                        || ('status-dataset'[status_date] > MAX('Dates'[date]) && 'status-dataset'[status]="closed"))
                                    )
                        )

 

Thank you so much @danrmcallister for putting me on the right track