Forum Discussion
Calculating incremental items sold v PY
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!
- 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.
3 Replies
V-lianl-msftCommunity Support
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.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
Super User
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
