Counting New Customers

[NimaiAhluwalia]

I need to build logic to get new accounts opened for example

Jan 10 accounts -- Bank started
Feb 15 accounts --- 5 new accounts as 10 accounts was of the previous month
March 21 accounts --- 6 new accounts as 15 accounts was of the previous month

Desired table as per the above logic

Month    New Accounts Opened
Jan                      10
Feb                      5
March                 6

And should be filtered from the the Month and Year column from the date table

I have the account number column and the account open date which is connected to the date table

Thanks for your help in advance

Data

Model

@amitchandak 
@tex628 
@selimovd 
@parry2k 
@Fowmy 

[v-yalanwu-msft · Accepted solution]

Hi, @NimaiAhluwalia ;

Your logic is to use this month count - previous month count = new accounts, right? If so ,you could create a measure as follows:

New Accounts Opened =
CALCULATE (
    COUNT ( [Account Number] ),
    FILTER (
        ALL ( 'Table (2)' ),
        EOMONTH ( [Acct opn date], 0 ) = EOMONTH ( MAX ( [Acct opn date] ), 0 )
    )
)
    - CALCULATE (
        COUNT ( [Account Number] ),
        FILTER (
            ALL ( 'Table (2)' ),
            EOMONTH ( [Acct opn date], 0 ) = EOMONTH ( MAX ( [Acct opn date] ), -1 )
        )
    )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi there. Let's see if I understand all. You can leave the open date against the DimDate relation inactive. It doesn't look like that's the way the data should flow when you filter your model. That inactive relationship can help us create a measue that can read it. The function is USERELATIONSHIP

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

You can build something like this:

NewMeasure =
CALCULATE(
    DISTINCTCOUNT('edw FACTCSG'[Account Number])
    , USERELATIONSHIP('edw FACTCSG'[Acct_opn_date], DimDate[Date])
)

I hope that helps,

[NimaiAhluwalia]

Hello @ibarrau 

Thanks for your reply I don't want to use that relationship there is already an active relationship with it.


Regards,

[ibarrau]

I know you already have one working the way it is suposed to work. However in order to get the number of new customers in a month you NEED to use the inactive relationship between those dates so you can filter the DimDate and get the count of accounts that are new (as your requirement says). That will only flow in the DAX Measure. It won't make a mess of anything else because it's an inactive relationship. That's a Power Bi Strength. If you don't do that the solution for the issue will probably be a DAX Monster instead of that small good practice.

I hope that helps