Verification between dates

[Syndicate_Admin]

Let's imagine that I have the following table:

Code	cod_int	date
CT1	seg_client	1/4/2024
CT1	seg_client	3/6/2024
CT1	seg_client	18/6/2024
CT2	seg_client	3/3/2024

I also have the following measurement;

# Clientes con visitas fuera de rango = 
 CALCULATE(
    COUNTROWS('MovInteracciones_sql'),
    (('MovInteracciones_sql'[Date] < DATE(2024, 6, 1) || MovInteracciones_sql[Date] > DATE(2024,6,15) ) && MovInteracciones_sql[Interaction Template Code]="SEG_CLIENT" 
))

Well, what this measurement does is check if there are any CTs with a visit lower on the 1/6 date and higher than 15/6. But what I need that I don't know how to do, is to verify that between the dates there is no visit, if there is the CT should not appear on the list.

In the end my final inquiry should come out

CT2 -> has a visit less than 1/6 or more than 15/6 and has no visit between those two (1/6 to 15/6)

Thank you.

[Anonymous · Accepted solution]

Thanks for the reply from @amitchandak , please allow me to provide another insight: 

Hi  @Syndicate_Admin ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _column=
SELECTCOLUMNS(
    FILTER(ALL('MovInteracciones_sql'),
    'MovInteracciones_sql'[date]>=DATE(2024,6,1) && 'MovInteracciones_sql'[date]<=DATE(2024,6,15)&&'MovInteracciones_sql'[cod_int]="seg_client"),"test",[Code])
return
CALCULATE(
    COUNTROWS(
        'MovInteracciones_sql'),
        FILTER('MovInteracciones_sql',
        NOT('MovInteracciones_sql'[Code]) in _column)
)

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

[Syndicate_Admin]

It seems to be going great. Thank you!

[amitchandak]

@Syndicate_Admin ,

Assuming this counting the client visited # Clientes con visitas fuera de rango

Not visited

Countx(Values(MovInteracciones_sql[cod_int]), if (isblank([# Clientes con visitas fuera de rango] ), [cod_int], blank() ))

Or try like

Clientes Sin Visitas en Rango =
VAR DateStart = DATE(2024, 6, 1)
VAR DateEnd = DATE(2024, 6, 15)

RETURN
CALCULATE(COUNTROWS(DISTINCT('MovInteracciones_sql'[Code])),
FILTER('MovInteracciones_sql','MovInteracciones_sql'[Interaction Template Code] = "SEG_CLIENT" &&
NOT(CALCULATE(COUNTROWS('MovInteracciones_sql'),'MovInteracciones_sql'[Date] >= DateStart && 'MovInteracciones_sql'[Date] <= DateEnd
) > 0)),'MovInteracciones_sql'[Date] < DateStart || 'MovInteracciones_sql'[Date] > DateEnd)

very similar tot lost customer

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q