Forum Discussion

findingsolution's avatar
findingsolution
Helper I
4 years ago

Historical New Customer Measure

Hi there,

 

I currently have a measure to calculate a 'new customer' (new unique emails seen in the last 6 months) in my sales data. However, I can't work out how to modify this measure so that I can see how many 'new customers' there were in Jan, Feb etc (a point in time view). I want to see the growth or decline of this customer segment over time (preferably in a bar chart). Can someone help with my measure?

 

New Customers = 
VAR currentCustomers = VALUES('Sales'[email])
VAR currentdate = EDATE(today(),-6)
VAR pastCustomers = CALCULATETABLE(VALUES('Sales'[email],ALL('Sales'[payment_date].[Month],'Sales'[payment_date].[MonthNo],'Sales'[payment_date].[Year]),'Sales'[payment_date]<currentdate)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

 

I have tried changing VAR currentdate = EDATE(today(),-6) to VAR currentdate = EDATE("31/01/2022",-6) to get new customers from January, but I think it is also including all the people that are new after January as well, so I cannot get a point in time view of my measure. 

 

Thank you!

Need Help

4 Replies

    • findingsolution's avatar
      findingsolution
      Helper I
      4 years ago

      I can't share the file because the data is sensitive, but this is what the current measure's output is:

       

      But you can see when I try and fix the date (in the second half of my message), this happens:

       

       

       

      Happy to try any other types of measures that would get me to the same result.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        Not only will i need a file to work with, I will also need to know the expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi findingsolution ,

     

    //I have tried changing VAR currentdate = EDATE(today(),-6) to VAR currentdate = EDATE("31/01/2022",-6) to get new customers from January

     

    Try replacing TODAY() in the formula with MAX('Sales'[payment_date]) .

    If this doesn't work for you, please consider sharing more details.

     

    similar question.

    Solved: Get the list of User form the previous month not i..

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data