Forum Discussion

GRT9791's avatar
GRT9791
Frequent Visitor
1 year ago
Solved

Count non recurrent customers

Hello, I have to solve a complex calculation. I have a Date table and a table of client accesses to an application (Accesos). I need to create a table visualization in Power BI with the Year-Month ...
  • johnt75's avatar
    johnt75
    1 year ago

    This might work

    Number of first time clients =
VAR ClientsWithFirstAccess =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( '01.Accessos'[Cliente_Digital] ),
            "@first access", CALCULATE ( MIN ( '01.Accessos'[fecha_hora_operacion] ) )
        ),
        ALLSELECTED ( '02. Fecha' )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( '01.Accessos'[Cliente_Digital] ),
        KEEPFILTERS (
            TREATAS (
                ClientsWithFirstAccess,
                '01.Accessos'[Cliente_Digital],
                '02. Fecha'[05. Fecha]
            )
        )
    )
RETURN
    Result

    It first builds a table of all clients and their first access times within the selected period, and then uses it as a filter, merging with any existing filter on the date table.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

You could create a new calculated column in '01.Accessos' to store the first access for each client,

First access =
CALCULATE (
    MIN ( '01.Accessos'[fecha_hora_operacion] ),
    ALLEXCEPT ( '01.Accessos', '01.Accessos'[Cliente_Digital] )
)

Create an inactive relationship from the new column to '02. Fecha'[05. Fecha] and then create a measure like

Number of first time clients =
CALCULATE (
    DISTINCTCOUNT ( '01.Accessos'[Cliente_Digital] ),
    USERELATIONSHIP ( '02. Fecha'[05. Fecha], '01.Accessos'[First access] )
)
GRT9791's avatar
GRT9791
Frequent Visitor
1 year ago

It doesn´t work because I need the first access in the selected period. Not the fist access of the customer in all the dates

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    This might work

    Number of first time clients =
VAR ClientsWithFirstAccess =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( '01.Accessos'[Cliente_Digital] ),
            "@first access", CALCULATE ( MIN ( '01.Accessos'[fecha_hora_operacion] ) )
        ),
        ALLSELECTED ( '02. Fecha' )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( '01.Accessos'[Cliente_Digital] ),
        KEEPFILTERS (
            TREATAS (
                ClientsWithFirstAccess,
                '01.Accessos'[Cliente_Digital],
                '02. Fecha'[05. Fecha]
            )
        )
    )
RETURN
    Result

    It first builds a table of all clients and their first access times within the selected period, and then uses it as a filter, merging with any existing filter on the date table.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi GRT9791,
    I wanted to check if you had the opportunity to review the information provided by johnt75. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank you.