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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Fraze
Helper I
Helper I

Moving Average using Existing Measure Calculation

Hello,

 

Doing some work on calculating Inventory Turnover Days. Using the Measure (A) and raw data (B) at the bottom, I am successfully able to calculate the Inventory Turnover Days as seen in the visual monthly and quarterly:

 

Fraze_0-1644439537956.png

 

Fraze_1-1644439600256.png

 

However, I cannot seem to create a measure that calcualtes the 3 month moving average. How would I convert my current measure, or create a new one that would give me the moving average.

 

EXAMPLE

 

For example, the 3 month running average for Jan 2022 - following the measure formula below - should be

Average Inventory = [(95 + 71 + 41) + (94 + 95 + 71)]/ (3 * 2) = 78

SaleCount = (93 + 59 + 62) = 214

Period Length = (31 + 31 + 30) = 91 

 

Inventory Turnover = (78 / 214) * 91 = 33

 

(A) MEASURE

 

Inventory Turnover =
var AverageInventory = DIVIDE(SUM([StartingInventory]) + SUM([EndingInventory]),DISTINCTCOUNT([Month])*2)
var SaleCount = SUM([RetailSales])
var PeriodLength = SUM([Count of Days])

RETURN

DIVIDE(AverageInventory,SaleCount)*PeriodLength
 
 

(B) RAW DATA

 

MonthRetailSalesStartingInventoryEndingInventoryCount of Days
January 1, 202293959431
December 1, 202159719531
November 1, 202162417130
October 1, 202170584131
September 1, 202189695830
August 1, 2021111556931
July 1, 2021118635531
June 1, 2021134696330
May 1, 2021102786931
April 1, 2021105997830
March 1, 20211481399931
February 1, 202111115813928
January 1, 20219114115831
December 1, 20207611514131
November 1, 202010611611530
October 1, 20201218911631
September 1, 2020140988930
August 1, 2020134719831
July 1, 2020154897131
June 1, 20201411158930
May 1, 202011514611531
April 1, 20206814814630
March 1, 20205010414831
February 1, 2020839310429
January 1, 2020102829331
December 1, 201990788231
November 1, 201991937830
October 1, 20191221229331
September 1, 201912313512230
August 1, 201913215813531
July 1, 201911116915831
June 1, 201910616716930
May 1, 201910213516731
April 1, 20197911613530
March 1, 201911712011631
February 1, 20198313612028
January 1, 20199715113631

 

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

Hi @Fraze 

 

Here is a link to run you through it: https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

 

In this blog, they show you how to do three months as well.

 

Hope this helps.

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

5 REPLIES 5
Fraze
Helper I
Helper I

Opening a new ticket with the reference table from the start. thanks for the feeback thus far, ill accept Theo's comment as the solution.

 

https://community.powerbi.com/t5/Desktop/Converting-Calculated-Measure-to-Moving-Average/m-p/2331401...

TheoC
Super User
Super User

Hi @Fraze 

 

Here is a link to run you through it: https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

 

In this blog, they show you how to do three months as well.

 

Hope this helps.

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Hey Theo,

 

Thanks for the material.

 

Using the approach from your article, I created the measure you will find below; However, it is not creating a moving average of anyting. The end result ends up exactly the same as the orginal measure. Any idea why that would be the case? Is seems to still just run one month at a time rather than applying the period specified in the DatesInPeriod variable

 

Inventory Turnover (3Mos Average) =
VAR NumOfMonths = 3
VAR LastSelectedDateMAX([Date])
VAR PeriodDATESINPERIOD([Date],LastSelectedDate, -NumOfMonths,MONTH)

VAR AverageInventory DIVIDE(SUM([StartingInventory])+SUM([EndingInventory]),DISTINCTCOUNT([Month])*2)
VAR SaleCountSUM([RetailSales])
VAR PeriodLength = DIVIDE(SUM([Count of Days]),DISTINCTCOUNT([Deal Type]))
VAR Result = CALCULATE(DIVIDE(AverageInventory,SaleCount)*PeriodLength,Period)

RETURN
IF('(4)Inventory Data'[Inventory Turnover] > 0, Result)

 

Fraze_0-1644447249267.png

 

@Fraze that's really interesting.  If it's coming out the exact same, then it may be the way you have your relationships / Data Model established.  Can you send a screenshot of the relationships and just provide an understanding of the way you've established your Date table?

 

Thanks in advance.

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

How about I just share the sample file link.

 

Basically the 3Mos Average should not be the same since it is using a calculate and a specified date range. Please let me know wher I went wrong

 

https://1drv.ms/u/s!AuZqEDLABUeQgs4umqr7okhOnjzpDQ?e=WdXSuk

 

Fraze_0-1644473844464.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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