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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.