Calculate sales before,during and after promorion period

[Mike91]

Hi All,

i need your helpe, i try to calculate for each item involed in promotion, the 4 week sales before, during the promotion and the 4 week after. i want use the promotion like a filter, to be able chose the promotion and analyze the impact in the sales. i tried different approach but i can't reach my goal specially to calculate the periodo before and after the promotion.

Thanks all

[Anonymous · Accepted solution]

Thank you Ritaf1983 

Hi, @Mike91 

Based on the PBIX file you provided, I use the following two DAX expressions to create a measure to find the date 4 weeks before the promotion and the date 4 weeks after the promotion.

Four Week before = 
VAR _start_day = MAX('Promo'[Start Date])
var _before_four_week = _start_day - 28
RETURN _before_four_week

Four Week after = 
VAR _end_day = MAX('Promo'[End Date])
var _after_four_week = _end_day + 28
RETURN _after_four_week

I created a measure for each of these three phases:

Four week before sales = 
VAR _end_day = MAX('Promo'[Start Date])
RETURN CALCULATE(SUM('Sales'[Sales]),FILTER('Sales','Sales'[Date]>[Four Week before]&&'Sales'[Date]<_end_day))

Four week after sales = 
VAR _start_day = MAX('Promo'[End Date])
RETURN CALCULATE(SUM('Sales'[Sales]),FILTER('Sales','Sales'[Date]>_start_day&&'Sales'[Date]<[Four Week after]))

During promotion sales = 
VAR _start_day = MAX('Promo'[Start Date])
VAR _end_day = MAX('Promo'[End Date])
RETURN CALCULATE(SUM(Sales[Sales]),FILTER('Sales','Sales'[Date]>=_start_day&&'Sales'[Date]<=_end_day))

Modify the table relationship as follows:

The results are as follows:

Best Regards

Jianpeng Li

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

[Mike91]

it's working, but if i try to add another Item, without any promotion this "appear" in my table to see only the pormotion perfomance.

[Ritaf1983]

Hi @Mike91 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

[Mike91]

Hi Rita,  this is the link where you can find the power bi structure it's the same i use in my original works. 

https://drive.google.com/file/d/1MkQ9F9n1AXpZuRkwzpF1WNdiZ6XNX7yQ/view?usp=drive_link