Count non recurrent customers

[GRT9791]

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 dimension and the calculation of clients who made their first access to the application in that month. The report has a filter to select the period I want to visualize, for example, from 01/01/2024 to 28/02/2024.

Therefore, if a customer accessed the application in January and also in February, I should only count in January. If a customer accessed it for the first time in February, I count him in February. 

Here, there are my date table, Accesos table and mi model in Power BI.

Thanks a lot

I try to do somethis like this, but it doesn´t work:

Measure =
var vFMinPeriodo=CALCULATE(MIN('02. Fecha'[05. Fecha]),ALLEXCEPT('02. Fecha','02. Fecha'[05. Fecha]))
var vFMaxPeriodo=CALCULATE(MAX('02. Fecha'[05. Fecha]),ALLEXCEPT('02. Fecha','02. Fecha'[05. Fecha]))
var vFisrtAccess=
CALCULATE(
MIN('01. Accesos'[fecha_hora_operacion]),
VALUES('01. Accesos'[num_doc]),
ALL('01. Accesos'[fecha_hora_operacion]),
FILTER(ALL('02. Fecha'),'02. Fecha'[05. Fecha]>=vFMinPeriodo &&'02. Fecha'[05. Fecha]<=vFMaxPeriodo)
)

var vAccess=SELECTEDVALUE('01. Accesos'[fecha_hora_operacion])
RETURN
CALCULATE(
COUNT('01. Accesos'[num_doc]),
'01. Accesos'[fecha_hora_operacion] = vFisrtAccess,
'02. Fecha'[05. Fecha]>=vFMinPeriodo &&'02. Fecha'[05. Fecha]<=vFMaxPeriodo)

[v-saisrao-msft]

Hi @GRT9791,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @GRT9791,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[johnt75]

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]

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

[v-saisrao-msft]

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.

[johnt75 · Accepted solution]

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.