cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maj
Helper I
Helper I

Average OOS by Weekly Periods

Hello,

 

I have an Excel file that lists out of stocks (EMPTY_SHELF_EVNTS) by WEEK_NAME (sample below). I created a date table, referenced each WEEK_NAME with the appropriate Week Of then merged those data sources in Power Query Editor (i.e. WEEK_NAMEExcel.PNG 2019 PD 12 WK 1 (45) is linked to Week Of 12/8/19). The file always includes a rolling 52 weeks.  What I'm trying to do now is create a measure that gives me the average of out of stocks over 52 weeks/26 weeks/12 weeks/4 weeks/Current week (i.e. Think about an Excel pivot table with each of the 52 weeks as columns and the OOS as values.  I want a measure that sums the total OOS then gives an average over all 52 weeks, another measure that sums the total OOS then gives an average over the past 26 weeks, etc.)

 

I created a measure to sum the total OOS:

Sum of EMTPY_SHELF_EVENTS = SUM('OOS By Division'[EMPTY_SHELF_EVENTS])

 

I attempted the weekly averages measures with the following calculations, but I'm not getting the expected outcome.  It seems I'm not able to use the Sum of EMPTY_SHELF_EVENTS measure in these weekly averages measures.

* 52 Week OOS = CALCULATE(AVERAGE('OOS by Division'[EMPTY_SHELF_EVENTS]), DATESINPERIOD('Date Table Group By'[Week Of], LASTDATE('Date Table Group By'[Week Of]),-364,DAY))

* 26 Week OOS = CALCULATE(AVERAGE('OOS by Division'[EMPTY_SHELF_EVENTS]), DATESINPERIOD('Date Table Group By'[Week Of], LASTDATE('Date Table Group By'[Week Of]),-182,DAY))

 

Is anyone able to help me figure out the correct dax formula for this? Thanks so much!

1 REPLY 1
amitchandak
Super User
Super User

@maj, Please refer this file, I have used Week rank to bring in week related data

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.