Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.