Calculating incremental items sold v PY

[abristow]

Hello,

I'm trying to calculate two similar (but different) metrics based on my sales data and need help with both, please.

1. [Incremental SKU] = a new item sold into a customer that has purchased other products in the past (i.e. total historical vol > 0)

2. [New SKU] = an item sold into a customer that has never purchased anything in the past (i.e. total historical vol =0)

In this case, the "past" is constrained to Q4 of prior year. 

This a super simple example of what I'm looking for in results:

CustomerNum	Date	SKU	Vol	Incremental SKU	New SKU
101	11/1/2019	Chocolate	3
101	11/5/2019	Vanilla	5
109	1/10/2020	Chocolate	1		1
109	1/10/2020	Vanilla	1		1
101	1/12/2020	Chocolate	2
101	1/12/2020	Banana	1	1

Thanks so much!

[V-lianl-msft · Accepted solution]

Hi @abristow ,

Once you have solved the problem of the new customer, you can proceed to deal with the problem of repurchasing other products.

You can refer to this pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[abristow]

Thanks for the help, @V-lianl-msft! 

One follow-up question: when I go to put this into my existing model, I cannot reference my date table with 'Date'[Date] as I do in other instances. Any thoughts/insights on why that would be and how to get around it? 

[amitchandak]

Please Try

Incremental SKU Calc = if(ISBLANK(COUNTX(FILTER(Sheet1,Sheet1[CustomerNum]=EARLIER(Sheet1[CustomerNum]) && Sheet1[SKU]=EARLIER(Sheet1[SKU]) && Sheet1[Date]<EARLIER(Sheet1[Date])),Sheet1[Date])) && [New SKU Calc] <>1,1,BLANK())
New SKU Calc New SKU Calc = if(ISBLANK( COUNTX(FILTER(Sheet1,Sheet1[CustomerNum]=EARLIER(Sheet1[CustomerNum]) && Sheet1[Date]<EARLIER(Sheet1[Date])),Sheet1[Date])),1,BLANK())

Please find the pbix :https://www.dropbox.com/s/f56p3zokducbvno/newSKU.pbix?dl=0

You will see a small difference because in this subset 101 is also new once and 101, vanilla is incremental SKU.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin