Lift of sales based on promo activity

[vk_18]

Hi All

I want to calculate lift of sales bsaed on promo activity. Activities are on week basis

i have the data at an order level as follows

SKUid	Activity	Date(Week)	Sales
10	A	22-02-2022	150
20	A	22-02-2022	100
15	B	15-10- 2022	40
10	no	2-08-2022	5
25	B	15-10-2022	15
20	no	2-08-2022	50
25	no	15-01-2022	10
10	no	6-05-2022	15

Now i need to calculate lift of activity(avg sales per week) when skus are in that activity from when they are not in activity

Activity	avg sales per week of activity	avg sales per week not activity	lift	lift %
A	100+150	5+50+15	(100+150)-(5+50+15)	(180)/(5+50+15)
B	40+15	10	40+15- (10)	(45)/(25)

So lift of activity is basically calculated as average sales per week os skus for that activity and subtracting avg sales per week of those  skus only when not in activity

Can you help me with the DAX for the same
i was able to calculate DAX for avg sales per week of activity
But am unable to calculate avg sales per week when not in activity

Thanks

[lbendlin]

Your expected result doesn't match your sample data. For SKU 25 the non-activity value should be 10, not 25.

[Sahir_Maharaj]

Hello @vk_18,

Can you please try this:

AverageSalesNotInActivity =
VAR SkusInActivity =
    CALCULATETABLE(
        VALUES('Table'[SKUid]),
        'Table'[Activity] = EARLIER('Table'[Activity])
    )
VAR SkusNotInActivity =
    EXCEPT(VALUES('Table'[SKUid]), SkusInActivity)
RETURN
    AVERAGEX(
        SUMMARIZE('Table', 'Table'[Date(Week)], "TotalSales", SUM('Table'[Sales])),
        IF(COUNTROWS(INTERSECT(SkusNotInActivity, SkusInActivity)) = 0, [TotalSales], BLANK())
    )

Should you need further assistance please don't hesitate to reach out to me.