Distinct Count of a separate Running Total measure

Question

I'm trying to determine a distinct count of customer id from an overall running total of outstanding service requests.

My table contains these columns: RequestID, CustomerID, OpenDate, ResolvedDate

A CustomerID can have multiple service requests.

Currently I had created two Running Total's of Incoming Requests and Resolved Requests, and then subtracting the two values to get the Running Total "outstanding service requests"

Running Total Service Requests:=
CALCULATE (
    COUNTA( Requests[RequestID] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Running Total Resolved Requests:=
CALCULATE (
    COUNTA( Requests[ResolveDate] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Running Total Unresolved:=[Running Total Service Requests]-[Running Total Resolved Requests]

Is there a better way of doing this? On top of this Running Total, how can I return the running total of unique customer ID's based on my previous measures? That is, out of all my outstanding service requests, how many distinct customers does it contain?

Aldert Aldert · Accepted Answer · 2019-06-10T07:15:25

You can make one column:

Running Customer Service Requests = CALCULATE(COUNTROWS(Requests),FILTER(EquityMarkets,Requests[CustomerID] = EARLIER(Requests[CustomerID]) && 'Date'[Date] <= MAX ( 'Date'[Date] )))

This will calculate all open requests per customer. same you can do for Resolved.

A second option is to create an extra table with CustomerID and do the counting there, then you need to do a Groupby.

CustomerTable= GROUPBY(Requests;Requests[CustomerID], "OpenRequests",COUNTX(CURRENTGROUP(),'Date'[Date] <= MAX ( 'Date'[Date] )))

Distinct Count of a separate Running Total measure

1 Answers