cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

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

1 ACCEPTED SOLUTION
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!

5 REPLIES 5
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...

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!

Helper I

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)

Super User

@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?

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!

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.