topic Filtering for "NEW" customers in DAX Commands and Tips

Filtering for "NEW" customers

cbolling — Fri, 09 Apr 2021 19:08:21 GMT

Hi All,

I want to have a table with customers and their revenue broken out by quarters. I only want to show them if their first time doing business with us is within the most recent 4 quarters (Thats what we considered "NEW"). I have a date filter, so I want this to interact with the date filter. So if I set the filter to the previous quarter, I could see who was categorized as "NEW" last quarter.

Here is a measure I created, but it is not working properly:

NEW CUSTOMER =

VAR LastSaleDateSlicer = CALCULATETABLE ( LASTNONBLANK ( DimDate[DateKey], 0 ), ALLSELECTED(FactSales))

VAR TwelveMonthsPrior = EDATE(LastSaleDateSlicer,-12)

RETURN

SWITCH(TRUE(),

SUMX(

FILTER(FactSales,FORMAT(TwelveMonthsPrior,"YYYYMM")>FORMAT(FactSales[RevenueRecognitionDate],"YYYYMM")),

FactSales[Revenue])

<= 0

SUMX(

FILTER(FactSales,FORMAT(TwelveMonthsPrior,"YYYYMM")<=FORMAT(FactSales[RevenueRecognitionDate],"YYYYMM")),

FactSales[Revenue])

> 0, "NEW",

"OLD")

Thank you for your help!

Re: Filtering for "NEW" customers

Jihwan_Kim — Fri, 09 Apr 2021 19:21:27 GMT

Hi, cbolling

Please correct me if I wrongly understood your question.

In the measure,

VAR this quarter's customers list can be created

VAR previous three quarters' customers' list can be created

VAR by using except DAX function, during 4 quarters, only the current quarter's customers list can be created.

By using this, new customer analysis can be performed.

if it is OK with you, please share your sample pbix file's link here, then I can try to come up with a more accurate measure.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Re: Filtering for "NEW" customers

cbolling — Fri, 09 Apr 2021 20:43:37 GMT

Unfortunately we do not have a sample PBIX file that we can give you, but would you please expand on the above variables that you would create?

From my understanding, you would do the following:

VAR QuarterToDateSales (I have this measure created in my PBIX file)

VAR PreviousThreeQuarterSales (I do not know how to calculate this, I wiuld love it if you could show me how to do this one)

And then you would somehow say, If sales for all quarters except the current and previous quarters is zero, then customer is new.

Please help by providing possible sample DAX.

Thank you!

Re: Filtering for "NEW" customers

Anonymous — Fri, 09 Apr 2021 22:33:59 GMT

// Let's assume that you have a HIDDEN column // called [CalendarYearQtrID] in // your DimDate table. This is an int // that numbers quarters in all years // sequentially without gaps. It starts // with 1 and goes up to the very number // of quarters you have in your table. // It's not 1, 2, 3, 4 only. It goes up // and can even be 20 if the number of // quarters in the table is >= 20. [Customer Type] = // This returns a value if one and only one // customer is visible in the current context. if( HASONEVALUE( Customer[CustomerID] ), var vCurrentQtrID = MAX( DimDate[CalendarYearQtrID] ) var vMaxVisibleDate = MAX( DimDate[Date] ) var vSalesWithinLast3QtrsPlusQTD = CALCULATE( [Sales], DimDate[CalendarYearQtrID] <= vCurrentQtrID, DimDate[CalendarYearQtrID] >= vCurrentQtrID - 3, DimDate[Date] <= vMaxVisibleDate ) var vSalesBeforeLast3QtrsPlusQTD = CALCULATE( [Sales], DimDate[CalendarYearQtrID] < vCurrentQtrID - 3, REMOVEFILTERS( DimDate ) ) var vNewCustomer = vSalesWithinLast3QtrsPlusQTD > 0 && vSalesBeforeLast3QtrsPlusQTD <= 0 return IF( vNewCustomer, "New", "Old" ) )

Re: Filtering for "NEW" customers

Jihwan_Kim — Sat, 10 Apr 2021 03:05:58 GMT

Hi, cbolling

I tried to create the sample by myself, however, the sample is a weekly basis. I could not create the quarterly basis sample.

But I hope you can easily follow the steps and concept of how I created the below table.

Please keep in mind that, in my sample, the week number is only within the same year. If you want to compare it with the previous year's week, the formula has to be a little bit different.

The reason is that, in my sample, Week4 minus 8 weeks ago is not Week-4, but it is last year's week48.

https://www.dropbox.com/s/xzln25lbo7mnhwo/cbolling.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Re: Filtering for "NEW" customers

cbolling — Mon, 12 Apr 2021 16:15:49 GMT

Thank you for your help! I will take a look!