Avg formula works on Non Promoted weeks but not on Promoted weeks to calculate incremental sales...

[krider71]

Hello,

My goal is to calculate incremental sales on promotion week compared to a rolling 13wk Non Promoted sales week and ultimately get a lift %.  Will also want to do something similar with the types of Ads we ran but first things first.

I have a column in my SalesVT table that is called Promotions and each row is labeled "Non Promo" or "Promo" if the brand was promoted.  I have created a powerbi table that has Non Promo and Promo on the columns and week end down the rows. 

1. Pulled in the measures Retail Sales by either the Non Promo and Promo week 
2.  Pulled in a measure that calculates the Non Promoted Rolling 13wk Sales with a measure called Avg Non Promo Retail $ 13Wk Rolling Avg (see formula below) (took me forever to find an formula online that would work and ignore weeks with no non promoted sales). IT actually works and I double checked. The problem is it only works if the Promo and Non Promo filter is on the page which I can live with.
3. Created a measure called incremental sales (see formula below):  
4. I get the correct information for the NON Promo Columns but it gives me a totally different Non promo Rolling Avg number under the Promo columns and therefore, incorrect incremental sales.

How do I get the Non Promo/baseline weekly sales to be the same in the Promoted columns as it is in the Non Promo column and calculate the correct incremental sales??

Formulas:

Avg Non Promo Retail $ 13Wk Rolling Avg =

var Startday= min(Dates[Date])-90

var endday = max(Dates[Date])

return

calculate (Retail Sales],SalesVT[PROMOTIONS]="Non Promo",DATESBETWEEN(Dates[Date],Startday,endday),REMOVEFILTERS(SalesVT[Week Number]))/CALCULATE(DISTINCTCOUNT(SalesVT[Week Number]),DATESBETWEEN(Dates[Date],Startday,endday), REMOVEFILTERS(SalesVT[Week Number]) )

Incremental Sales = [Retail Sales]-[Avg Non Promo Retail $ 13Wk Rolling Avg]

This is the result:

Tried another way:

Created this measure that takes my measure that calculates Retail sales on Promo weeks and it actually calculates the correct incremental sales but UNDER the NonPromo columns, ugh

Incremental Sales 1 = [Retail Ad Promo $]-[Avg Non Promo Retail $ 13Wk Rolling Avg]

Help! And thank you!

[krider71]

Thank you for the reply.  I must be doing something wrong as the formula just gives me the exact same number as my sales for that day and not an average for past weeks.

This is my week rank formula - I even tried the other formula and get the exact same numbers.

Week Rank = RANKX('Dates','Dates'[Week of Year],,ASC,Dense) //YYYYWW format

Not sure what I am doing wrong....

[amitchandak]

@krider71 , what is that last Date = max(Date) , that will not work. I used weekday, so that for Monday  you get all Monday

[krider71]

the (Date) is just my date

[amitchandak]

@krider71 , yes, I got it. If you say Date = Max([Date]) then we will not get data beyond that row, so remove that, I used weekday, so that I only get the same weekday for the last 13 weeks

[amitchandak]

@krider71 , with help of week rank you can get Avg measure like

Last 13 weeks Week Day = CALCULATE(AverageX(Values(Date[Week Rank] ), calculate( sum(Sales[value]))), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank]) && 'Date'[WeekDay]=max('Date'[WeekDay])))

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format