Calculate monthly moving average including blank values

[marius_04]

Hi all, 

I want to create a rolling average with a window of 6 months.

The following measure:

6-month average Sales Impact = 
IF(ENDOFMONTH('Calender'[Date].[Date])>TODAY(), BLANK(), 
 (
 IF( 
 ISFILTERED('Calender'[Date]),
 VAR __LAST_DATE = ENDOFMONTH('Calender'[Date].[Date])    
 VAR __DATE_PERIOD =        
       DATESBETWEEN(
           'Calender'[Date].[Date],
           STARTOFMONTH(DATEADD(__LAST_DATE, -5, MONTH)),
           __LAST_DATE
       )
    RETURN
        AVERAGEX(
          CALCULATETABLE(
            SUMMARIZE(
           VALUES('Calender'), 
           'Calender'[Date].[Year],
           'Calender'[Date].[QuarterNo],
           'Calender'[Date].[Quarter],
           'Calender'[Date].[MonthNo], 
           'Calender'[Date].[Month]    
          ),
          __DATE_PERIOD
       ),
       CALCULATE(
          SUM('Input Opportunity'[Sales Impact (EUR)]),
          ALL('Calender'[Date].[Day])
       )       
    )
)))

In general it works, but I get stuck with one issue:

For blank values (sales impact column B) the average is wrong calculated (column C) because PBI does not take into account the fields with blank values:

So for example, for March, PBI takes the sum of October to March, which is correct, but divides this by 4 and not by 6, because just 4 fields have values.

But I would like to include the blank values, so the sum of the last 6 month shall always be devided by 6 regardless of how many fields have values (column D).

Can one hint me on the correct approach?

[amitchandak]

@marius_04 , then you do sumx in place of averagex and divide by 6 ?

[marius_04]

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

[dudeyates · Accepted solution]

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

[marius_04]

 @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