Forum Discussion

Fraze's avatar
Fraze
Helper I
4 years ago
Solved

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:

 

 

 

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

 

Need Help
Show and Tell
Tips and Tricks

5 Replies

    • Fraze's avatar
      Fraze
      Helper I
      4 years ago

      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's avatar
        TheoC
        Community Champion
        4 years ago

        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