Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NimaiAhluwalia
Responsive Resident
Responsive Resident

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 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

NimaiAhluwalia_0-1628161678853.png

 

Model

NimaiAhluwalia_1-1628161738120.png

 

@amitchandak 
@tex628 
@selimovd 
@parry2k 
@Fowmy 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1628485117081.png

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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1628485117081.png

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
Super User
Super User

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,


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

Happy to help!

LaDataWeb Blog

Hello @ibarrau 

 

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


Regards,

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 


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

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors