Forum Discussion
H_insight
Helper V
Helper VNew 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...
danextian
Super User
Super UserHi 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_insightHi 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