Forum Discussion
Calculate monthly moving average including blank values
Hi marius_04
I was working up a moving average yesterday based on below vid. Begin at the 5:50 mark, since I'm not going to be able to say it better.
https://www.youtube.com/watch?v=3VajEecHMSs
---Updated---Well, my newly created moving margin was suffering from the same issue as yours. Thanks for helping me notice that! 😉
You could add 6 months to the minimum selected date in your date range, then check if the current context date is >= to your "new" minimum date.
For instance, making two changes to the code from the vid shared above worked for me... // Created this variable which adds 14 days to the minimum selected date VAR __MinDateSelected = CALCULATE( MIN('Dates'[Date]) + 14, ALLSELECTED('Dates'[Date]) ) ... RETURN // Once the current context date is >= to my new minimum date, then move on in the process IF( MAX(Dates[Date]) >= __MinDateSelected, IF( MAX(Dates[Date]) <= __LastSalesDate, __Result ) )
Now my moving average line begins 14 days into the chart.Hope this helps with your first 6 months issue.
James
amitchandak thanks for your solution. It worked! But unfortunately I have noticed another issue now. Do you maybe also have a hint for that?
My data is starting in January 2018, so I want to have my first 6-month-average in June 2018.
My graph is showing now all averages from Jan 2018 to Aug 2021. And the averages for Jan 2018 to May 2018 are wrong of course, because they are devided by 1, 2,...,5 and not by 6.
How can I fix the problem so that my graph is supposed to show the Average only from June 2018, but takes the Vvalues for calculation already from Jan 2018.
If I filter the graph (from June 2018) the problem is unfortunately only shifted to the back.
My graph right now, which I would like to show only from June 2018.
Hi marius_04
I was working up a moving average yesterday based on below vid. Begin at the 5:50 mark, since I'm not going to be able to say it better.
https://www.youtube.com/watch?v=3VajEecHMSs
---Updated---
Well, my newly created moving margin was suffering from the same issue as yours. Thanks for helping me notice that! 😉
You could add 6 months to the minimum selected date in your date range, then check if the current context date is >= to your "new" minimum date.
For instance, making two changes to the code from the vid shared above worked for me
...
// Created this variable which adds 14 days to the minimum selected date
VAR __MinDateSelected = CALCULATE( MIN('Dates'[Date]) + 14, ALLSELECTED('Dates'[Date]) )
...
RETURN
// Once the current context date is >= to my new minimum date, then move on in the process
IF( MAX(Dates[Date]) >= __MinDateSelected,
IF(
MAX(Dates[Date]) <= __LastSalesDate,
__Result
)
)
Now my moving average line begins 14 days into the chart.
Hope this helps with your first 6 months issue.
James
dudeyates thanks a lot four your proposal. The video was great and I was able to fix the problem.
At the end, I used the following:IF( ENDOFMONTH(Calender[Date].[Date]) < DATE(2018,06,01), BLANK(),​
And, maybe you faced the same problem, at the end of the graph, the average goes beyond today's date (6 months in my case). With the following expression, you limit the average to the current date:
IF(ENDOFMONTH('Calender'[Date].[Date])>TODAY(), BLANK(),
Thanks for your support and Best