Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
(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 |
Solved! Go to 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
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
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.
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
@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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |