New and Lost Users

[beaoliv123-_]

Hi everyone,

I would like to ask you if you can help me calculate New Users and Lost Users.

I have a table will all user id from the beggining until now.

I want to know, for example, in the last year/month/week/day, how many user id was new? I mean, how many user id was never found in the table until now, according to my time specification.

Also, I would like to calculate, how many stopped to appear. I want to consider a lost user someone who hasn't visited me in 3 months. I also want to calculate how many do not visit me in 2 months, and 1 month.

I want to be able to see how many of them and I also want to be able to see which user id is considered lost / new.

I hope I was clear,

Thank you so much  

[v-jayw-msft · Accepted solution]

Hi @beaoliv123-_ ,

Create an independent date slicer table.

Use VALUES() function to get a list of IDs in selected date period.

Then you could use IDs in table to compare with this list.

For example to count the IDs not in selected period.

countID =
VAR _list =
    CALCULATETABLE (
        VALUES ( 'table'[ID] ),
        FILTER (
            ALLSELECTED ( 'table' ),
            'table'[date] >= MIN ( slicer[date] )
                && 'table'[date] <= MAX ( slicer[date] )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'table'[ID] ),
        FILTER ( ALLSELECTED ( 'table' ), NOT ( 'table'[ID] IN _list ) )
    )

If you need detailed formula, please share some sample data and expected result.

Best Regards,

Jay

[PC2790]

Here is an elaborative article by radacad that you cna refer to cater to your requirement