Help with 'New Business, 'Returning Business' and 'Lost Business' DAX Calculations

[Anonymous]

Hi Community

This one has baffled me for a while now. 

I'm trying to come up with the right method and DAX for calculating the following:

• New Business - Total Sales Amount on an Item & Customer combination that sells for the first time in a rolling 12 month period.
• Returning Business - Total Sales Amount in 24 months on Item & Customer combination that has ordered in the previous 12 months and has prior 12 months.
• Lost Business - Total Sales Amount for Item and Customer comination that hasn't ordered in 12 months but has in prior 12 months.

I have a date table at the ready, and all of my Sales Data is in one table. So the model is very simple. I'm using SSAS Tabular Live Connection, so any changes would be back end.

Any other posts I've found online are looking at number of customers, and aren't specific to 'Item and Customer' combination.

Appreciate your help.

Thanks

Martin

[Anonymous · Accepted solution]

// Assumptions:
// 1. Dates table marked as the Data table in the model.
// 2. Sales as the fact table.
// 3. Customers as a dimension.
// 4. Products as a dimension.
// You should always, without exceptions, create correct
// dimensional models. Only then will you be sure that
// your measures work correctly in all circumstances.
// You don't aim at a model that stores everything in one
// table. Rather, you aim at a proper dimensional design.

// 1) New Business - Total sales for unique visible combinations
// 	  (CustomerId, ProductId) such that the product sells for
//    the first time in a rolling 12-month period, meaning the
//    combination is not present in the past beyond the period,
//    whatever the past contains (might be empty).
// 2) Returning Business - Total sales in 24 months for unique
//    combinations (CustomerId, ProductId) that are present
//    also in the 12-month period preceding the 24 months.
//    The 12-month period must be present in the calendar in full.
// 3) Lost Business - Total sales for the unique visible combinations
//    (CustomerId, ProductId) which are not present in the past 12
//    months but are present in the prior 12 months. The prior 12 months
//    must be present in the calendar.
//
// Let's assume that there is a measure that returns the total sales
// for any slicing:

[Total Sales] = SUM( Sales[Amount] )

// Based on the above:

[New Business] =
var __lastVisibleDate = MAX( Dates[Date] )
var __rolling12Months =
	DATESINPERIOD(
		Dates[Date],
		__lastVisibleDate,
		-12,
		MONTH
	)
var __shouldCalculate = 
	// We have to make sure that the rolling
	// period does indeed have 12 months,
	// not fewer. It might not be 12 months
	// if we are too close to the left end of
	// the calendar.
	CALCULATE(
		DISTINCTCOUNT( Dates[MonthID] ) = 12,
		__rolling12Months
	)
var __result = 
	if( __shouldCalculate,
		
		var __periodPriorTo12Months =
			EXCEPT(
				FILTER(
					ALL( Dates[Date] ),
					Dates[Date] <= __lastVisibleDate
				),
				__rolling12Months
			)
		var __customerProductIn12Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__rolling12Months
			)
		var __customerProductPriorTo12Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__periodPriorTo12Months
			)
		var __effectiveCustomerProduct =
			EXCEPT(
				__combinationsIn12Months,
				__combinationsPriorTo12Months
			)
		return
			// You have to decide what you want
			// to calculate here for the set
			// of pairs (CustomerId, ProductId).
			// Especially, what period of time
			// you want... I assume you want
			// the 12-month rolling period.
			CALCULATE(
				[Total Sales],
				__effectiveCustomerProduct,
				__rolling12Months,
				ALL( Customers ),
				AlL( Products ),
				ALL( Dates )
			)
	)
return
	__result
	
// I'll post the other measures when I get
// a chance to write them... Stay tuned!

[Anonymous · Accepted solution]

[Returning Business] =
var __lastVisibleDate = LASTDATE( Dates[Date] )
var __rolling24Months =
	DATESINPERIOD(
		Dates[Date],
		__lastVisibleDate,
		-24,
		MONTH
	)
var __shouldCalculate = 
	// We have to make sure that the rolling
	// period does indeed have 24 + 12 months,
	// not fewer.
	MAX(
		NEXTDAY( DATEADD( Dates[Date], -(24 + 12), MONTH ) ),
		DATEADD( NEXTDAY( Dates[Date] ), -(24 + 12), MONTH ))
	) > BLANK()
	
var __result =
	if( __shouldCalculate,
		
		var __periodPriorTo24Months =
			EXCEPT(
				DATESINPERIOD(
					Dates[Date],
					__lastVisibleDate,
					-(24 + 12),
					MONTH
				)
				__rolling24Months
			)
		var __customerProductIn24Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__rolling24Months
			)
		var __customerProductPriorTo24Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__periodPriorTo24Months
			)
		var __effectiveCustomerProduct =
			INTERSECT(
				__customerProductIn24Months,
				__customerProductPriorTo24Months
			)
		return
			CALCULATE(
				[Total Sales],
				__effectiveCustomerProduct,
				__rolling24Months,
				ALL( Customers ),
				AlL( Products ),
				ALL( Dates )
			)
	)
return
	__result

[Anonymous · Accepted solution]

// 3) Lost Business - Total sales for the unique visible combinations
//    (CustomerId, ProductId) which are not present in the past 12
//    months but are present in the prior 12 months. The prior 12 months
//    must be present in the calendar.

[Lost Business] =
var __lastVisibleDate = LASTDATE( Dates[Date] )
var __rolling12Months =
	DATESINPERIOD(
		Dates[Date],
		__lastVisibleDate,
		-(12),
		MONTH
	)
var __shouldCalculate = 
	// We have to make sure that the rolling
	// period does indeed have 24 + 12 months,
	// not fewer.
	MAX(
		NEXTDAY( DATEADD( Dates[Date], -(12 + 12), MONTH ) ),
		DATEADD( NEXTDAY( Dates[Date] ), -(12 + 12), MONTH ))
	) > BLANK()
	
var __result =
	if( __shouldCalculate,
		
		var __periodPriorTo12Months =
			EXCEPT(
				DATESINPERIOD(
					Dates[Date],
					__lastVisibleDate,
					-(12 + 12),
					MONTH
				)
				__rolling12Months
			)
		var __customerProductIn12Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__rolling12Months
			)
		var __customerProductPriorTo12Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__periodPriorTo12Months
			)
		var __effectiveCustomerProduct =
			EXCEPT(
				__customerProductPriorTo12Months,
				__customerProductIn12Months
			)
		return
			CALCULATE(
				[Total Sales],
				__effectiveCustomerProduct,
				// If we filter by the last 12 months,
				// we'll obviously get BLANK as these
				// combinations do not exist in the
				// last 12 months. We then need to use
				// the 12-month period before the rolling
				// one.
				__periodPriorTo12Months,
				ALL( Customers ),
				AlL( Products ),
				ALL( Dates )
			)
	)
return
	__result

[Anonymous]

@Anonymous 

This is why I'm thankful to the PBI community here. Thank you so much. 

Just one thing: I've implemented the measures and with a few tweaks, works perfectly. Except one thing I overlooked. 

The Returning Business was wrong in my original spec. I was imagining that I'd require 24 months in my measure but I actually just need 12 months. It's the lost business that would need to look in the 12-24 month period to work out the sum of sales lost. 

How would I adapt the measure for Returning Business to a 12 month period?

The Shouldcalculate variable confuses me slightly, var __shouldCalculate =
MAX(
NEXTDAY( DATEADD( 'Primary Date Table'[Date], -(24 + 12), MONTH ) ),
DATEADD( NEXTDAY( 'Primary Date Table'[Date]), -(24 + 12), MONTH ))
> BLANK()

Why would I need to add 24 + 12? Would I need to in this case?

Thanks in advance.

[Anonymous]

24 + 12 is based on your spec. You need to be able to go back in time 24 months and then 12 more. This means that the first day of the period after this operation has been performed must exist in the calendar. This is why you need this condition. If such a day is not present in the calendar, the output of the MAX(…) operation will be BLANK. To know why you need max( nextday(dateadd), dateadd(nextday) ) you have to study the way the functions work and consider the boundary conditions, that is, when the calendar has just 1 year in it, or just 2 years, or just 3...

I hope this clarifies everything.

[Anonymous]

Thanks for your response. 

So for the new definition:

Returning Business=

Total Sales Amount in 12 months on Item & Customer combination that has ordered in the previous 12 months and has in the prior 12 months.

Returning Business:=
var __lastVisibleDate = LASTDATE( 'Primary Date Table'[Date] )
var __rolling12Months = //Changed from __rolling24Months
	DATESINPERIOD(
		'Primary Date Table'[Date],
		__lastVisibleDate,
		-12, //Changed from -24
			MONTH
	)
var __shouldCalculate = 
	MAX(
		NEXTDAY( DATEADD( 'Primary Date Table'[Date], -(24 + 12), MONTH ) ),
		DATEADD( NEXTDAY( 'Primary Date Table'[Date]), -(24 + 12), MONTH ))
	 > BLANK()
	
var __result =
	if( __shouldCalculate,
		
		var __periodPriorTo24Months =
			EXCEPT(
				DATESINPERIOD(
					'Primary Date Table'[Date],
					__lastVisibleDate,
					-(24 + 12),
					MONTH
				),
				__rolling24Months
)
		var __customerProductIn24Months =
			CALCULATETABLE(
				SUMMARIZE(
					'Sales Flash',
					Customer[CustomerID],
					'Item'[ItemID]
				),
				__rolling24Months
			)
		var __customerProductPriorTo24Months =
			CALCULATETABLE(
				SUMMARIZE(
					'Sales Flash',
					Customer[CustomerID],
					'Item'[ItemID]
				),
				__periodPriorTo24Months
			)
		var __effectiveCustomerProduct =
			INTERSECT(
				__customerProductIn24Months,
				__customerProductPriorTo24Months
			)
		return
			CALCULATE(
				[Total Sales (R)],
				__effectiveCustomerProduct,
				__rolling12Months,//Changed from __rolling24Months
				ALL( 'Customer' ),
				AlL( 'Item' ),
				ALL( 'Primary Date Table' )
			)
	)
return
	__result

[Anonymous]

Nope. All 24's must be changed to 12's.

[Anonymous]

---

Hi @Anonymous 

Your help here to identify new, returning and lost Item+Customer sales has been so powerful.

I've now been asked to help apply the same logic to just the customers, without including the item in the calcuation. 

So instead of New Customers+Item, Returning Customer+Item, or Lost Customer+Item, it'll be just customer counts with the same logic around the date periods. 

I tried to modify your code here but SUMMARIZE function springs an error on Argument 3.

Your help is very appreciated. 

Thanks

[Anonymous]

You just have to get rid of any references to 'Item' from the code.

[Anonymous]

@Anonymous  Thank you Daxer, this is where I get the Summarize error when removing the ItemNo.

[Anonymous]

Are you saying that you get an error from:

SUMMARIZE(
    'Sales Flash',
    Customer[CustomerID]
)

If you do (but I seriously doubt it), then 'Sales Flash' is just not connected via *:1 to Customer.

[Anonymous]

You were right. I missed something. Thank you

[amitchandak]

@Anonymous , refer if this can help

new vs repeat customer
https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196
https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/
https://www.sqlbi.com/articles/computing-new-customers-in-dax/

[harshnathani]

HI @Anonymous ,

See if this helps.

https://www.youtube.com/watch?v=GK-W25RM87Q

Regrads,

Harsh Nathani

[Anonymous]

Hi, thanks, yes this is also something I've already looked at and what I'm really looking for is Sales value for item+customer combination.

[Anonymous · Accepted solution]

// Assumptions:
// 1. Dates table marked as the Data table in the model.
// 2. Sales as the fact table.
// 3. Customers as a dimension.
// 4. Products as a dimension.
// You should always, without exceptions, create correct
// dimensional models. Only then will you be sure that
// your measures work correctly in all circumstances.
// You don't aim at a model that stores everything in one
// table. Rather, you aim at a proper dimensional design.

// 1) New Business - Total sales for unique visible combinations
// 	  (CustomerId, ProductId) such that the product sells for
//    the first time in a rolling 12-month period, meaning the
//    combination is not present in the past beyond the period,
//    whatever the past contains (might be empty).
// 2) Returning Business - Total sales in 24 months for unique
//    combinations (CustomerId, ProductId) that are present
//    also in the 12-month period preceding the 24 months.
//    The 12-month period must be present in the calendar in full.
// 3) Lost Business - Total sales for the unique visible combinations
//    (CustomerId, ProductId) which are not present in the past 12
//    months but are present in the prior 12 months. The prior 12 months
//    must be present in the calendar.
//
// Let's assume that there is a measure that returns the total sales
// for any slicing:

[Total Sales] = SUM( Sales[Amount] )

// Based on the above:

[New Business] =
var __lastVisibleDate = MAX( Dates[Date] )
var __rolling12Months =
	DATESINPERIOD(
		Dates[Date],
		__lastVisibleDate,
		-12,
		MONTH
	)
var __shouldCalculate = 
	// We have to make sure that the rolling
	// period does indeed have 12 months,
	// not fewer. It might not be 12 months
	// if we are too close to the left end of
	// the calendar.
	CALCULATE(
		DISTINCTCOUNT( Dates[MonthID] ) = 12,
		__rolling12Months
	)
var __result = 
	if( __shouldCalculate,
		
		var __periodPriorTo12Months =
			EXCEPT(
				FILTER(
					ALL( Dates[Date] ),
					Dates[Date] <= __lastVisibleDate
				),
				__rolling12Months
			)
		var __customerProductIn12Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__rolling12Months
			)
		var __customerProductPriorTo12Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__periodPriorTo12Months
			)
		var __effectiveCustomerProduct =
			EXCEPT(
				__combinationsIn12Months,
				__combinationsPriorTo12Months
			)
		return
			// You have to decide what you want
			// to calculate here for the set
			// of pairs (CustomerId, ProductId).
			// Especially, what period of time
			// you want... I assume you want
			// the 12-month rolling period.
			CALCULATE(
				[Total Sales],
				__effectiveCustomerProduct,
				__rolling12Months,
				ALL( Customers ),
				AlL( Products ),
				ALL( Dates )
			)
	)
return
	__result
	
// I'll post the other measures when I get
// a chance to write them... Stay tuned!

[Anonymous]

Of course, this condition

var __shouldCalculate = 
	// We have to make sure that the rolling
	// period does indeed have 12 months,
	// not fewer. It might not be 12 months
	// if we are too close to the left end of
	// the calendar.
	CALCULATE(
		DISTINCTCOUNT( Dates[MonthID] ) = 12,
		__rolling12Months
	)

 works correctly only for the days that are the ends of months. The beginning of the code should be changed to this

[New Business] =
var __lastVisibleDate = LASTDATE( Dates[Date] ) // LINE CHANGED
var __rolling12Months =
	DATESINPERIOD(
		Dates[Date],
		__lastVisibleDate,
		-12,
		MONTH
	)
var __shouldCalculate = 
	// We have to make sure that the rolling
	// period does indeed have 12 months,
	// not fewer. It might not be 12 months
	// if we are too close to the left end of
	// the calendar.
	MAX(
		NEXTDAY( DATEADD( Dates[Date], -1, YEAR ) ),
		DATEADD( NEXTDAY( Dates[Date] ), -1, YEAR ))
	) <> BLANK()
	
var __result = ... // same stuff as above

[Anonymous]

WOW! I'm blown away by this. It worked! 

A few changes to  __customerProductIn12Months,
__customerProductPriorTo12Months and linking to my dim customer and dim products table and we're good!

Thank you so much!

Can't wait for the rest.

[Anonymous · Accepted solution]

[Returning Business] =
var __lastVisibleDate = LASTDATE( Dates[Date] )
var __rolling24Months =
	DATESINPERIOD(
		Dates[Date],
		__lastVisibleDate,
		-24,
		MONTH
	)
var __shouldCalculate = 
	// We have to make sure that the rolling
	// period does indeed have 24 + 12 months,
	// not fewer.
	MAX(
		NEXTDAY( DATEADD( Dates[Date], -(24 + 12), MONTH ) ),
		DATEADD( NEXTDAY( Dates[Date] ), -(24 + 12), MONTH ))
	) > BLANK()
	
var __result =
	if( __shouldCalculate,
		
		var __periodPriorTo24Months =
			EXCEPT(
				DATESINPERIOD(
					Dates[Date],
					__lastVisibleDate,
					-(24 + 12),
					MONTH
				)
				__rolling24Months
			)
		var __customerProductIn24Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__rolling24Months
			)
		var __customerProductPriorTo24Months =
			CALCULATETABLE(
				SUMMARIZE(
					Sales,
					Customers[CustomerID],
					Products[ProductID]
				),
				__periodPriorTo24Months
			)
		var __effectiveCustomerProduct =
			INTERSECT(
				__customerProductIn24Months,
				__customerProductPriorTo24Months
			)
		return
			CALCULATE(
				[Total Sales],
				__effectiveCustomerProduct,
				__rolling24Months,
				ALL( Customers ),
				AlL( Products ),
				ALL( Dates )
			)
	)
return
	__result

[Anonymous]

Thank you, but these are the posts I was refering to as not specific to Customer+Item, and using Sales Amount with the logic I've mentioned above.