Need help with query - Sum based on select criteria with filtered date

[apmulhearn]

Hello,

I need to be able to get a distinct count of the number of clients who satisfy the following criteria:

Based on the date range provided in the slider,

How many clients have 1 or more trips with End Date Prior to OR Equal to that latest date in that date range where the status is NOT Cancelled OR Transferred

AND

The Trip Code does NOT start with X or F.

Sample Data below... MANY thanks for help!

ClientId     EndDate      TripCode    Status

12345	1/1/2017	XA908	Completed
6789	1/12/2017	BA807	Completed
5432	2/4/2017	XA908	Cancelled
65432	2/10/2017	SA306	Transferred
98076	6/10/2018	FA432	Completed
543678	4/3/2019	BA999	Completed
76543	4/2/2018	BA346	Completed
4325678	1/7/2018	SA456	Completed
4356	5/2/2019	PEA095	Completed
35689	2/1/2021	PA043	Booked
23145	4/2/2016	PA434	Completed
65879	5/4/2019	XA908	Cancelled
34903	5/2/2018	BA807	Booked
12345	3/2/2019	XA908	Completed
6789	2/19/2018	SA306	Completed
5432	5/6/2019	FA432	Cancelled
65432	7/4/2019	BA999	Transferred
98076	3/5/2018	BA346	Completed
543678	2/1/2020	SA456	Completed
76543	4/2/2019	PEA095	Completed
4325678	5/3/2019	PA043	Transferred
4356	2/1/2019	PA434	Completed
35689	6/23/2017	XA908	Completed
23145	11/3/2017	PA043	Completed
65879	6/1/2019	PA434	Cancelled
34903	7/16/2018	FA432	Completed
12345	5/10/2019	XA908	Completed
6789	4/13/2020	BA807	Booked
5432	7/17/2017	XA908	Booked
65432	10/23/2021	SA306	Transferred
98076	9/23/2019	FA432	Booked
543678	10/18/2018	BA999	Cancelled
76543	5/3/2019	BA346	Completed
4325678	6/6/2018	SA456	Completed
4356	7/8/2019	PEA095	Completed
35689	10/4/2018	PA043	Completed
23145	5/1/2021	PA434	Completed
65879	8/16/2020	BA999	Completed
34903	9/17/2019	BA346	Completed
6789	4/10/2018	BA664	Cancelled
6789	2/10/2018	XA444	Completed

[Fowmy]

@apmulhearn 

Create the following measure:

Count of Clients = 
var __enddate = MAX(Table3[End Date]) return

CALCULATE(
    COUNT(Table3[Client ID]),
    Table3[End Date] <= __enddate,
    NOT Table3[Status] IN {"Cancelled","Transferred"},
    FILTER(
        DISTINCT(Table3[Trip Code]),
        NOT LEFT(Table3[Trip Code],1) IN {"X","F"}
    ) 
)

[apmulhearn]

Hi @Fowmy 

I really appreciate the quick help...but in looking at your screenshot, I don't think that can be exactly what I'm looking for. Client ID 76543 only exists in the raw data 3 times, but your count shows 3 rows with that ID, one with a count of 2. 

Using the dates you've selected, this is the outcome I would have been seeking:

[v-yiruan-msft · Accepted solution]

Hi @apmulhearn ，

You can create a measure as below to get the count of clients who meet the filter criteria, it will only get 10 distinct clients. Please check the below table, client 5432 and 12345 should not be in the list, as their TripCode start with "X".Client 65879 has one row which meet the filter criteria. You can find the attachment for the details.

Note: I create a date dimension table, its date field be used in slicer.

Count of clients = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ClientId] ),
    FILTER (
        'Table',
        'Table'[EndDate] <= MAX ( 'Date'[Date] )
            && NOT ( 'Table'[Status] IN { "Cancelled", "Transferred" } )
                && NOT ( LEFT ( 'Table'[TripCode] ) IN { "X", "F" } )
    )
)

Best Regards

[Fowmy]

@apmulhearn 

I am not at the PC now, change my COUNT to DISTINCTCOUNT and try