How to calculate New Members and renuals based on Below scenario

[kiranch]

Hi Team,

I need to do the calculation based on Below  Scenario, Can you Please help me to resolve this.

Regards,

Kiran

[v-xiaoyan-msft · Accepted solution]

Hi @kiranch ,

You can meet your needs by the following measures：

Status =
VAR _count =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Customer ID] ) )
VAR _last =
    MAXX (
        FILTER ( ALL ( 'Table' ), [Customer ID] = MAX ( 'Table'[Customer ID] ) ),
        [Start Date]
    )
RETURN
    IF (
        _count > 1
            && CALCULATE (
                MAX ( 'Table'[End Date] ),
                FILTER (
                    'Table',
                    [Customer ID] = MAX ( 'Table'[Customer ID] )
                        && [Start Date] = _last
                )
            )
                = BLANK (),
        "Renewal",
        "New Member"
    )

Count of New =
VAR _t =
    SUMMARIZE ( 'Table', [Customer ID], "Status", [Status] )
RETURN
    COUNTROWS ( FILTER ( _t, [Status] = "New Member" ) )

Count of Renewal =
VAR _t =
    SUMMARIZE ( 'Table', [Customer ID], "Status", [Status] )
RETURN
    COUNTROWS ( FILTER ( _t, [Status] = "Renewal" ) )

Then you can get a result like：

A demo for your reference is attched below.

Hope it helps,

Best Regards,
Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[kiranch]

Hi @v-xiaoyan-msft ,

Thanks for the reply,

I want to find the New customers and Renewal customers Based on the Below data,

If you check the data , customerID 1000001,100002 are renewal customers and 1000003 is New Customer.

Here Total Customers are 3 and Renewal Customers are  2 and New customer is 1

Regards,

Kiran

[v-xiaoyan-msft]

Hi @kiranch ,

I may not quite understand what you mean, is the information you provided above your sample data? Could you explain your requirements in more detail, it would be nice to have more information about what your expected output is?

Best Regards,
Community Support Team _ Caitlyn