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

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

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

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.