Forum Discussion

osinquinvdm's avatar
osinquinvdm
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Cumulative measure – the open helpdesk request use case

   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 ...
Need Help
  • osinquinvdm's avatar
    osinquinvdm
    9 years ago

    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

danrmcallister's avatar
danrmcallister
Icon for Resolver II rankResolver II
9 years ago

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's avatar
    osinquinvdm
    Icon for Advocate II rankAdvocate II
    9 years ago

    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