Moving Average using Existing Measure Calculation

[Fraze]

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:

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

Month	RetailSales	StartingInventory	EndingInventory	Count of Days
January 1, 2022	93	95	94	31
December 1, 2021	59	71	95	31
November 1, 2021	62	41	71	30
October 1, 2021	70	58	41	31
September 1, 2021	89	69	58	30
August 1, 2021	111	55	69	31
July 1, 2021	118	63	55	31
June 1, 2021	134	69	63	30
May 1, 2021	102	78	69	31
April 1, 2021	105	99	78	30
March 1, 2021	148	139	99	31
February 1, 2021	111	158	139	28
January 1, 2021	91	141	158	31
December 1, 2020	76	115	141	31
November 1, 2020	106	116	115	30
October 1, 2020	121	89	116	31
September 1, 2020	140	98	89	30
August 1, 2020	134	71	98	31
July 1, 2020	154	89	71	31
June 1, 2020	141	115	89	30
May 1, 2020	115	146	115	31
April 1, 2020	68	148	146	30
March 1, 2020	50	104	148	31
February 1, 2020	83	93	104	29
January 1, 2020	102	82	93	31
December 1, 2019	90	78	82	31
November 1, 2019	91	93	78	30
October 1, 2019	122	122	93	31
September 1, 2019	123	135	122	30
August 1, 2019	132	158	135	31
July 1, 2019	111	169	158	31
June 1, 2019	106	167	169	30
May 1, 2019	102	135	167	31
April 1, 2019	79	116	135	30
March 1, 2019	117	120	116	31
February 1, 2019	83	136	120	28
January 1, 2019	97	151	136	31

[Fraze]

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 · Accepted solution]

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

[Fraze]

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 LastSelectedDate = MAX([Date])

VAR Period = DATESINPERIOD([Date],LastSelectedDate, -NumOfMonths,MONTH)

VAR AverageInventory = DIVIDE(SUM([StartingInventory])+SUM([EndingInventory]),DISTINCTCOUNT([Month])*2)

VAR SaleCount = SUM([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)

[TheoC]

@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

[Fraze]

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