Removing Promotional Data From Sales

[rcarmichael1990]

Good afternoon,

I have a data set in PQ which comprises of a list of dates, product codes, the sales quantity, and a flag (where an item on promotion is denoted by a number greater than 0) which shows whether the specific item was on promotion on the specific date:

Date	Product	Sale Qty	Promo Flag
22 Oct 2023	Prod01	8	0
23 Oct 2023	Prod01	9	0
24 Oct 2023	Prod01	4	0
25 Oct 2023	Prod01	5	0
26 Oct 2023	Prod01	9	0
27 Oct 2023	Prod01	10	0
28 Oct 2023	Prod01	8	0
29 Oct 2023	Prod01	6	0
30 Oct 2023	Prod01	5	0
31 Oct 2023	Prod01	11	3
1 Nov 2023	Prod01	17	6
2 Nov 2023	Prod01	6	1
3 Nov 2023	Prod01	19	4
4 Nov 2023	Prod01	15	3
5 Nov 2023	Prod01	14	5
6 Nov 2023	Prod01	16	4
7 Nov 2023	Prod01	2	0
8 Nov 2023	Prod01	12	5
9 Nov 2023	Prod01	22	4
10 Nov 2023	Prod01	19	5
11 Nov 2023	Prod01	27	5
12 Nov 2023	Prod01	12	3
13 Nov 2023	Prod01	30	4
14 Nov 2023	Prod01	0	1
15 Nov 2023	Prod01	12	4
16 Nov 2023	Prod01	11	4
17 Nov 2023	Prod01	22	5
18 Nov 2023	Prod01	24	6
19 Nov 2023	Prod01	17	3
20 Nov 2023	Prod01	16	0
21 Nov 2023	Prod01	10	0
22 Nov 2023	Prod01	11	0
23 Nov 2023	Prod01	13	0
24 Nov 2023	Prod01	44	0
25 Nov 2023	Prod01	78	0

I would like to derive baseline sales, ie replace the sales where the product was on promotion with the average of the previous non-promo qty, and next non-promo entry (preferably smoothing between them, but i'll settle for the average across each day).

For example, in the data set above I would have the 08 Nov through 19 Nov be the average of 2 and 16, so each of the dates between 08 Nov and 19 Nov would show 9; or even better, 8th would be 3, 9th would be 4... 18th would be 13, 19th would be 14.

I'm unsure of how to reference the previous & next  [Promo Flag] = 0 to average... or a better alternative.

Any assistance is greatly appreciated.

Sincere regards,

Ryan

[AlienSx · Accepted solution]

let
    // duration step
    one_day = #duration(1, 0, 0, 0),
    // function to generate list of dates and sales
    calc = (x, y, n) => 
        [daily_sales = (y{1} - x{1}) / n, 
        lst = List.Zip({List.Dates(x{0} + one_day, n - 1, one_day), List.Numbers(x{1} + daily_sales, n - 1, daily_sales)})][lst],
    // Source data - replac with yours
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tp = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    // filter out promo sales and get list from table
    nopromo_table = Table.SelectRows(tp, (x) => x[Promo Flag] = 0),
    nopromo = List.Buffer(Table.ToList(nopromo_table, (x) => {x{0}, x{2}})),
    // generate list of missing dates and sales 
    generate = List.Generate(
        () => [i = 0, s = nopromo{0}, n = 0, res = {}],
        (x) => x[i] < List.Count(nopromo),
        (x) => 
            [
                i = x[i] + 1, 
                s = nopromo{i}, 
                n = Duration.Days(s{0} - x[s]{0}), 
                res = if n <= 1 then {} else calc(x[s], s, n)
            ],
        (x) => x[res]
    ), 
    // generated list >> to table
    baseline_sales = Table.FromList(List.Combine(generate), (x) => x, {"Date", "Sale Qty"}),
    // combine and sort new sales table, fill down product column
    all = Table.Sort(nopromo_table & baseline_sales, "Date"), 
    fd = Table.FillDown(all, {"Product"})
in
    fd

[rcarmichael]

Thank you! I'll need to read through it a few more times to understand how you acomplished some of the steps.

[bhanu_gautam]

@rcarmichael1990 , 

Here’s a step-by-step guide:

Create a Calculated Column for Non-Promo Sales: First, create a calculated column that only includes sales quantities for non-promotion days. This will help in identifying the previous and next non-promotion sales quantities.
NonPromoSales = IF('Table'[Promo Flag] = 0, 'Table'[Sale Qty], BLANK())

Create a Calculated Column for Previous Non-Promo Sales: Create a calculated column to get the previous non-promotion sales quantity.

PreviousNonPromoSales =
VAR CurrentDate = 'Table'[Date]
RETURN
CALCULATE(
MAX('Table'[NonPromoSales]),
FILTER(
'Table',
'Table'[Date] < CurrentDate && NOT(ISBLANK('Table'[NonPromoSales]))
)

Create a Calculated Column for Next Non-Promo Sales: Create a calculated column to get the next non-promotion sales quantity.
NextNonPromoSales =
VAR CurrentDate = 'Table'[Date]
RETURN
CALCULATE(
MIN('Table'[NonPromoSales]),
FILTER(
'Table',
'Table'[Date] > CurrentDate && NOT(ISBLANK('Table'[NonPromoSales]))
)

Create a Calculated Column for Baseline Sales: Finally, create a calculated column that calculates the baseline sales by averaging the previous and next non-promotion sales quantities.
BaselineSales =
IF(
'Table'[Promo Flag] > 0,
DIVIDE('Table'[PreviousNonPromoSales] + 'Table'[NextNonPromoSales], 2),
'Table'[Sale Qty]
)

[rcarmichael]

Thank you for your reply!

Unfortunately as the code wasn't what Power Query accepts in the "Custom Column" dialogue, I was unable to proceed with your solution; 

ie RETURN, CALCULATE, MIN, & FILTER.