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.
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??
Tried another way:
Help! And thank you!
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.
Not sure what I am doing wrong....
@krider71 , what is that last Date = max(Date) , that will not work. I used weekday, so that for Monday you get all Monday
the (Date) is just my date
@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
@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)
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.