Counting distinct records (and the most updated) within a date range selected by user

[pezakas]

Hello community!

Here is my data sample:

CST_ID	REF_DT	FRST_NM	LST_NM	ADDRSS
1001	1/1/2023	PAUL	SMITH	HERE 16
1002	10/1/2023	JOHN	WHITE	THERE 12
1003	10/1/2023	STACY	BROWN	THERE 15
1004	2/2/2023	OLIVER	ROSE	UP 205
1005	2/2/2023	NICK	MILES	DOWN 14
1006	2/2/2023	SAMANTHA	RODRIGUEZ	UP&DOWN 25
1007	2/2/2023	IRENE	MORALES	BEHIND 10
1008	2/2/2023	LULU	DAVIS	INFRONT 12
1009	2/2/2023	THEOBOLD	LAWRENCE	FARAWAY 120
1001	15/3/2023	PAUL	SMITH	DOWN 15
1010	16/3/2023	THALIA	APPLE	UP&DOWN 28
1011	30/3/2023	TAMARA	BOYD	UP&DOWN 56
1012	30/3/2023	GEORGE	ROBINSON	DOWN 19
1013	12/4/2023	NEIL	FLOYD	UP 298
1007	15/4/2023	IRENE	MORALES	FARAWAY 120
1015	17/4/2023	ALVIN	VARGAS	FARAWAY 121

I want to build a linechart like the below one:

I want to show the progress of the distinct count of the customers within a selected date range from a slicer.
As you can see in the table there are 2 records with same CST_ID. Thats because their adress has been updated. So i dont want to double count them, just count the most updated record. In case user tries drill through i want him to see the most updated info of the customer.
Thanks in advance!

[Anonymous · Accepted solution]

Hi  @pezakas ,

If your slicer selects less than April 14, 2023, then any data from April 15, 2023 will be filtered out and only the largest number of dates in the interval less than April 14, 2023 will be counted

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('Table'[REF_DT])
var _maxdate=
MAXX(
    FILTER(ALLSELECTED('Table'),
    'Table'[CST_ID]=MAX('Table'[CST_ID])),[REF_DT])
return
COUNTX(
    FILTER(ALL('Table'),    'Table'[REF_DT]=_maxdate&&YEAR('Table'[REF_DT])=YEAR(MAX('Table'[REF_DT]))&&MONTH('Table'[REF_DT])=MONTH(MAX('Table'[REF_DT]))),[CST_ID])

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

[devesh_gupta]

@pezakas 

You can use Power Query to create a new table that only includes the most recent record for each customer. Then, you can create a measure to count the distinct customers within the selected date range. Here are the steps:

1. Create a New Table with Most Recent Records:

   • Go to Edit Queries > Append Queries > Append Queries as New.
   • In the new query, sort the REF_DT column in descending order.
   • Use the Group By function on the CST_ID column and select All Rows for the aggregation.
   • Expand the All Rows column and keep only the first row for each group.

2. Create a Measure to Count Distinct Customers:

   • After creating the new table, you can create a measure using the DISTINCTCOUNT function to count the....
   • The DAX formula would look something like this:

Distinct Customer Count = DISTINCTCOUNT('NewTable'[CST_ID])​

If you find this insightful, please provide a Kudo and accept this as a solution.

[pezakas]

How is this solution gonna work in case the user select a range before 15/04/2023? Cause in this scenario, the most updated record for the CST_ID=1007 is the one on 02/02/2023. By keeping only the Top rows in another table in a decending order by CST_ID in the first place, i am gonna loose that record, right?