Measure to calculate new customer and new bank account

[atavo]

Hi,
I had tried to use this pattern (from Sqlbi site) to determine new customers but it does not work:

Sales[NewCustomers] =

    COUNTROWS (

        FILTER (

            CALCULATETABLE (

                ADDCOLUMNS (

                    VALUES ( Customer[CustomerKey] ),

                    "DateOfFirstBuy", CALCULATE ( MIN ( Sales[OrderDate] ) )

                ),

                ALL ( 'Date' )

            ),

            CONTAINS (

                VALUES ( 'Date'[FullDate] ),

                'Date'[FullDate],

                [DateOfFirstBuy]

            )

        )

    )

The issue may not be so much the pattern as is my "transaction" table.

My transaction table is not a proper transaction table in the sense that it is actually a snapshot of customer bank accounts every month that I subsequently consolidate and then run analysis on.

Appreciate any kind assistance on the following:

1. How do I determine the number of new customers based on reportdate and customerid?

2. How do I determine the number of new accounts based on reportdate and customerid?

Please see below on how the result should look.

[v-jiascu-msft]

Hi @atavo,

You can try it this way. It's obvious there are many other accesses too. You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgUmlXoHTfKCUM1-m.

1. Create a Date table -- Calendar.

Calendar =
CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) )

2. Create a relationship between 'Calendar'[Date] and 'Customer'[Reportdate]. The Cross filter direction is single.

New Customers =
VAR minMonth =
    CALCULATE (
        MONTH ( MIN ( Customer[ReportDate] ) ),
        ALL ( 'Calendar' ),
        ALL ( Customer )
    )
VAR monthlyCustomers =
    CALCULATETABLE (
        VALUES ( Customer[CustomerID] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( Customer[ReportDate] ), -1 ),
            -1,
            MONTH
        ),
        ALL ( Customer )
    )
RETURN
    IF (
        MONTH ( MIN ( 'Customer'[ReportDate] ) ) = minMonth,
        0,
        SUMX ( 'Customer', IF ( Customer[CustomerID] IN monthlyCustomers, 0, 1 ) )
    )

New Accouts =
VAR minMonth =
    CALCULATE (
        MONTH ( MIN ( Customer[ReportDate] ) ),
        ALL ( 'Calendar' ),
        ALL ( Customer )
    )
VAR monthlyCustomers =
    CALCULATETABLE (
        VALUES ( Customer[AccNo] ),
        DATESINPERIOD (
            'Calendar'[Date],
            EOMONTH ( MIN ( Customer[ReportDate] ), -1 ),
            -1,
            MONTH
        ),
        ALL ( Customer )
    )
RETURN
    IF (
        MONTH ( MIN ( 'Customer'[ReportDate] ) ) = minMonth,
        0,
        SUMX ( 'Customer', IF ( Customer[AccNo] IN monthlyCustomers, 0, 1 ) )
    )

Best Regards!

Dale

[atavo]

Hi Dale,

Please can the solution be adapted to excel 2013 (does not have VAR function).

Tried to recreate the variables as separate measures and stringing them together in an IF statement but I am way over my head here...

Alfred

[v-jiascu-msft]

Hi Alfred,

Try these formula below please. If they can help, please mark it as answer.

New Customers 2 =
IF (
    MONTH ( MIN ( 'Customer'[ReportDate] ) )
        = CALCULATE (
            MONTH ( MIN ( Customer[ReportDate] ) ),
            ALL ( 'Calendar' ),
            ALL ( Customer )
        ),
    0,
    SUMX (
        'Customer',
        IF (
            CONTAINS (
                CALCULATETABLE (
                    VALUES ( Customer[CustomerID] ),
                    DATESINPERIOD (
                        'Calendar'[Date],
                        EOMONTH ( MIN ( Customer[ReportDate] ), -1 ),
                        -1,
                        MONTH
                    ),
                    ALL ( Customer )
                ),
                Customer[CustomerID], Customer[CustomerID]
            ),
            0,
            1
        )
    )
)

New Accouts 2 =
IF (
    MONTH ( MIN ( 'Customer'[ReportDate] ) )
        = CALCULATE (
            MONTH ( MIN ( Customer[ReportDate] ) ),
            ALL ( 'Calendar' ),
            ALL ( Customer )
        ),
    0,
    SUMX (
        'Customer',
        IF (
            CONTAINS (
                CALCULATETABLE (
                    VALUES ( Customer[AccNo] ),
                    DATESINPERIOD (
                        'Calendar'[Date],
                        EOMONTH ( MIN ( Customer[ReportDate] ), -1 ),
                        -1,
                        MONTH
                    ),
                    ALL ( Customer )
                ),
                Customer[AccNo], Customer[AccNo]
            ),
            0,
            1
        )
    )
)

Best Regards,

Dale

[v-jiascu-msft]

Hi Alfred,

Try these formula below please. If they can help, please mark it as answer.

New Customers 2 =
IF (
    MONTH ( MIN ( 'Customer'[ReportDate] ) )
        = CALCULATE (
            MONTH ( MIN ( Customer[ReportDate] ) ),
            ALL ( 'Calendar' ),
            ALL ( Customer )
        ),
    0,
    SUMX (
        'Customer',
        IF (
            CONTAINS (
                CALCULATETABLE (
                    VALUES ( Customer[CustomerID] ),
                    DATESINPERIOD (
                        'Calendar'[Date],
                        EOMONTH ( MIN ( Customer[ReportDate] ), -1 ),
                        -1,
                        MONTH
                    ),
                    ALL ( Customer )
                ),
                Customer[CustomerID], Customer[CustomerID]
            ),
            0,
            1
        )
    )
)

New Accouts 2 =
IF (
    MONTH ( MIN ( 'Customer'[ReportDate] ) )
        = CALCULATE (
            MONTH ( MIN ( Customer[ReportDate] ) ),
            ALL ( 'Calendar' ),
            ALL ( Customer )
        ),
    0,
    SUMX (
        'Customer',
        IF (
            CONTAINS (
                CALCULATETABLE (
                    VALUES ( Customer[AccNo] ),
                    DATESINPERIOD (
                        'Calendar'[Date],
                        EOMONTH ( MIN ( Customer[ReportDate] ), -1 ),
                        -1,
                        MONTH
                    ),
                    ALL ( Customer )
                ),
                Customer[AccNo], Customer[AccNo]
            ),
            0,
            1
        )
    )
)

Best Regards,

Dale

[atavo]

Question 2 should be:

2. How do I determine the number of new accounts based on reportdate and AccNo?

Thanks