Average Sales of a certain condition in a Period of time excluding weeks that condition was not met

[krider71]

Hello - hoping an easy tweak:  

Here is my formula -- wish I created it but a long while ago somebody helped me.  

It works great for an average of my At Cost Retail within a period of time but I want to get the average for weeks NOT promoted for that period of time.  So, need to identify the column in my data called "Promotions"= "Non Promo" for both the numerator and then also the denominator.  

I have tried researching for hours...nothing I have tried works for my situation.  My data is all weekly data.

At Cost Avg Baseline 13 Wk =

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

var endday = max(Dates[Date])

return

calculate ([At Cost Retai $],DATESBETWEEN(Dates[Date],Startday,endday),REMOVEFILTERS(retailerVT[Week Number]))/CALCULATE(DISTINCTCOUNT(retailerVT[Week Number]),DATESBETWEEN(Dates[Date],Startday,endday), REMOVEFILTERS(retailerVT[Week Number]) )

Here is an exampl of my data

Based on my current formula I get an averge of $33,141 vs Non Promo Average of $17,961 (only 4 weeks average vs all 6 wks)

Week Ended      AT cost Retail$   PROMOTIONS

11/04/23	$19,119	Non Promo
11/11/23	$18,586	Non Promo
11/18/23	$17,931	Non Promo
11/25/23	$17,480	Promo
12/02/23	$16,207	Non Promo
12/09/23	$17,875	Promo

Thank you in advance!!

[amitchandak · Accepted solution]

@krider71 , if this measure do not have filter a new measure

M1= calculate([At Cost Retai $], filter(Table, Table[Promotions]= "Non Promo" ) )

Then have measure like

Avg = CALCULATE(AverageX(Values('Date'[Week Year]),[M1])
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-72,DAY))

Or have week Rank

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

These measures can help

Avg Last 6 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [M1]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[krider71]

Thank you i will try this.  I have never been able to totally grasp the week rank formulas.  

[krider71]

IT WORKED!!! SO SIMPLE 

THANK YOU