Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors