Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

Verification between dates

Let's imagine that I have the following table:

Codecod_intdate
CT1seg_client1/4/2024
CT1seg_client3/6/2024
CT1seg_client18/6/2024
CT2seg_client3/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's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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