cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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!!

1 ACCEPTED SOLUTION
Super User

@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

3 REPLIES 3
Super User

@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

Frequent Visitor

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

Frequent Visitor

IT WORKED!!! SO SIMPLE

THANK YOU

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors