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

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

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