Forum Discussion

Tony88's avatar
Tony88
Icon for Helper I rankHelper I
4 years ago

How to get running customer count on below situation?

Hello,  

 

Can you please help me simplify and find out what's wrong with the running count? 

 

I am trying to get the rolling account count by customer categories, the categories are Start Customer, New Customer, Lost Customer, and Ending Customer Count. The definition for those are below, 

Starting Customers (active last period and this period), lost (active last period but not this period), or new (not active last period but active this period).

 

The final result I want 

 

Since I have limited knowledge of Power BI, with help of one of my friends we created a solution. Please see the link. To get the result we have to create multiple tables and I don't think the number is correct. I am sure there is a simple way or using DAX query or Power query you can do this easily. 

 

Pbix file
https://drive.google.com/file/d/1ilq6-obpd_JXGRuBhEMW4yDAhOXm1alM/view?usp=share_link

Sample Data

https://drive.google.com/file/d/1HtVXmU7VZd848R3cwyJ8E7HaFMzfx51V/view?usp=share_link

 

Can anybody please take a look at my file and let me know the best we can do this? Also why the number is not correct? 

 

Thank you so much for your time and help.  

 

Need Help
Show and Tell
Tips and Tricks

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    How do we have to use the status column to calculate start, new, lost and ending?  Please clarify. 

  • Tony88's avatar
    Tony88
    Icon for Helper I rankHelper I
    4 years ago

    Hi Ashish_Mathur ,

     

    Thank you so much for taking look at my post. 

    So we have 4 statuses in the status column. Active, Draft, Expired, Terminated. Let's forget about or remove the draft. We don't use that. 
    Customers can have multiple contracts (Accounts).  so they’re only considered lost customers if they have no active contract. If they have 5 contacts and drop to 4, that’s not a lost account.

    The below Customer is Active
    0010h00001XSC2YAAX   Active
    0010h00001XSC2YAAX  Terminated

     

    I did build a logic using date and status 

    Starting Customers Count - Active last period and this period

    New Customer Count -  Not active last period but active this period

    Lost Customer Count - Active last period but not this period -(This is always a negative number) 

    Ending Customer Count - Is a sum of above 3 (Lost customer should be negative) 

     

    As you can see in my photo, the ending value of the previous period should start on the net period. I think we have to do a running count. I belive. This little confusing. 

     

    Please let me know if this makes any sense. 

     

    Thank you so much 🙏

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Please see if the new and lost count are correct.  Once you confirm their accuracy, I will work n the opening and closing.  Download the PBI file from here.

      • Tony88's avatar
        Tony88
        Icon for Helper I rankHelper I
        4 years ago

        Ashish_Mathur 

        Thank you so much for taking look at it. You are a genius. The number looks good. 

        This is the hardest part I think, this is where we go stuck.  In your method can we get the ending number for this period to start with the next period? 

        Don't we have to do a running count or something to get like the above picture? This is the hardest part for me. Please take a look at it when you get a moment.  Also, I am wondering if is it possible to drill up and down on the date, so the number will change. 

         

        This is where we stuck and started adding so many tables and got the wrong number in my file.

         

        Again, Thank you so much for your help and time. 🙏