Forum Discussion

NimaiAhluwalia's avatar
NimaiAhluwalia
Continued Contributor
5 years ago
Solved

Counting New Customers

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 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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's avatar
ibarrau
Super User
5 years ago

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's avatar
NimaiAhluwalia
Continued Contributor
5 years ago

Hello ibarrau 

 

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


Regards,

  • ibarrau's avatar
    ibarrau
    Super User
    5 years ago

    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