topic Re: Count of new cases in DAX Commands and Tips

Count of new cases

Sonya_Zam — Thu, 03 Nov 2022 12:50:38 GMT

Hi,

I currently have over ten years of data, and I am interested in counting the number of new clients who have not been seen in the 5 years preceding the first contact with a service during specific period. For example in 2021, there were 200 new consumers that utilised the service and won't seen over the last five years since their first contact. We currently have date at first contact, date the last contact and client ID.

Any help how to write the dax for this?

Thanks,

Re: Count of new cases

Anonymous — Thu, 03 Nov 2022 13:10:51 GMT

hello Sonya_Zam ,
if you could please provide with sample data

Re: Count of new cases

Anonymous — Fri, 04 Nov 2022 06:53:56 GMT

Hi Sonya_Zam ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 = CALENDAR(DATE(2020,1,1),DATE(2022,12,31))

2. Create measure.

Measure = var _date=SELECTEDVALUE('Table 2'[Date]) var _last5date=DATE(YEAR(_date)-5,MONTH(_date),DAY(_date)) var _dateinterval= SELECTCOLUMNS(FILTER(ALL('Table 2'),[Date]>=_last5date&&[Date]<=_date),"date1",[Date]) return CALCULATE(DISTINCTCOUNT('Table'[client ID]),FILTER(ALL('Table'), NOT( 'Table'[date at first contact] ) in _dateinterval))

3. Result:

If it doesn't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

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

Re: Count of new cases

Sonya_Zam — Sat, 05 Nov 2022 11:42:36 GMT

Below is a sample of the data.

I am trying to get the number of new clients that is defined as clients who has not been seen in the 5 years preceding the first service contact during the reference period.

For example, in 2021 there were two new clients A001 and E001 (within five years).

Thank you for your help.

ClientD	ContactDate
A001	01/11/2010
A001	02/11/2010
A001	04/11/2010
B001	05/06/2016
B001	07/06/2016
B001	08/06/2016
C001	08/02/2017
D001	01/03/2014
A001	03/11/2021
A001	04/11/2021
E001	02/04/2021
G001	…… (missing)
C001	02/06/2021

Re: Count of new cases

Sonya_Zam — Sat, 05 Nov 2022 11:44:02 GMT

Anonymous Please see above - I have provided a sample of the data. Thank you.

Re: Count of new cases

Anonymous — Sat, 05 Nov 2022 12:09:26 GMT

hello Sonya_Zam im still not sure i i understood correctly,
are you saying that you need to count the customers that have been inactive for past 5 years? if so please follow along:
create new columns:

Last Seen = CALCULATE(MAX(Table1[ContactDate]),ALLEXCEPT(Table1,Table1[ClientD]))

and for 5 years :

More than 5 years? = IF(Table1[Last Seen] < ((TODAY()) - 365* 5),"True","False")

you will have something like:

then create a measure :

Count of = CALCULATE(DISTINCTCOUNT(Table1[ClientD]),Table1[More than 5 years?] = "True")