Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
6 years ago
Solved

New and Recurring Customers

Hello,

I have a sample data that contains 8 columns. What I am trying to do is to identify new customers withing the current reporting month, but my measure is not returning the right outcome.

 

New Customer rule = New sales in the current reporting month only, and not before it. In the below example, If you look at the reporting month in Jan-15, we have 7 records. Of which 4 is new sales only, and the remaining 3 records belong to an old sale (they happened in Dec & Oct 2014 and not Jan 15). “Service Start” column indicates a new sale/service within the reporting month column.

I tried the below Dax to get the new customers:
New Customers =
VAR
CustomersList = VALUES( Data[ID] )
RETURN
CALCULATE( COUNTROWS( VALUES( Data[ID] ) ),
FILTER(
CustomersList,
CALCULATE( COUNTROWS( Data ),
FILTER( ALLSELECTED( ‘Date’), ‘Date’[Date] < MIN( ‘Date’[Date] ) ) ) = 0 ) )
However, the table outcome was:

The expected outcome to show how many new customers and values should be:

 

Sample pbix file

 

Any ideas?

Thanks

 

Need Help
  • H_insight's avatar
    H_insight
    6 years ago

    danextian 

     

    Thanks for your help on this. I have changed the OldSalesCount to:

    OldSalesCount = 
IF (
     EOMONTH(Data[Service Start],0) < EOMONTH(Data[Reporting Month],0),
    0,
    1
)

    and the result came as expected.

     

    Cheers

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 years ago

    Hi H_insight,

    If I understand correctly, if the YYYYMMDD of  Service Start < Reporting Date, it is an old sale.  Try these calculated columns

    OldSalesCount =
IF (
    VALUE ( FORMAT ( EOMONTH ( 'Table'[Service Start], 0 ), "YYYYMMDD" ) ) < 'Table'[ReportingDate],
    1,
    0
)

     

    Customer Status =
VAR OldSalesCount =
    //counts old sales per customer
    CALCULATE (
        SUM ( 'Table'[OldSalesCount] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer Number] )
    )
RETURN
    //if OldSalesCount = 0 then new customer
    IF ( OldSalesCount = 0, "new", "old" )


     

    • H_insight's avatar
      H_insight
      Icon for Helper V rankHelper V
      6 years ago

      Hi danextian ,

       

      Correct, if the YYYYMMDD of Service Start < Selected Reporting Date then it's an old customer. If YYYYMMDD of Service Start is within the Reporting Date dates, then it's a new customer. Please bear in mind that reporting month will always be the end of the month, so the service started could fall within the month.

       

      I have tried your Dax, but unfortunately not the right outcome.

       

      The outcome should look like:

      Thanks

    • H_insight's avatar
      H_insight
      Icon for Helper V rankHelper V
      6 years ago

      danextian 

       

      Thanks for your help on this. I have changed the OldSalesCount to:

      OldSalesCount = 
IF (
     EOMONTH(Data[Service Start],0) < EOMONTH(Data[Reporting Month],0),
    0,
    1
)

      and the result came as expected.

       

      Cheers

  • nhoward's avatar
    nhoward
    Icon for Resolver I rankResolver I
    6 years ago

    Hi Hesham,

     

    We'll need to know the tables and relationships in your data model before we can help. 

    • H_insight's avatar
      H_insight
      Icon for Helper V rankHelper V
      6 years ago

      Hi nhoward ,

       

      Here is the PBIX link & the sample file.

       

      The sample file holds a simple model of Data (fact) and Date table. Only one relationship between Reporting Month in Data table & Date in Date table.

       

      Please let me know if you require any additional information.

       

      Thanks

      Hesham