How to exclude/Ignore Blanks in Moving average calculation

[Anonymous]

Hi,

I have 4 months moving average calculation

Moving Avg 4 months = AVERAGEX(

DATESINPERIOD( 'sheet'[All Date], MAX('sheet'[All Date]), -4,MONTH),

[Calc Measure])

where [Calc Measure] is the calculated measure

All Date = STARTOFMONTH('sheet'[Dates])

the current output is

All Date	Calc Measure	Moving Avg 4 months
31/07/2016 0:00	1.961855	1.961855
28/08/2016 0:00	1.649799	1.805827
25/09/2016 0:00	1.246926	1.619526
30/10/2016 0:00	1.12559	1.496042
27/11/2016 0:00	1.063595	1.271477
25/12/2016 0:00	0.9302	1.091578
08/01/2017 0:00	0.812864	0.983062
05/02/2017 0:00	0.912609	0.929817
05/03/2017 0:00	0.894477	0.887538
02/04/2017 0:00	0.922033	0.885496
07/05/2017 0:00	1.012021	0.935285
04/06/2017 0:00	1.039191	0.966931
02/07/2017 0:00	1.085609	1.014714
06/08/2017 0:00	1.02102	1.03946
03/09/2017 0:00	1.042663	1.047121
08/10/2017 0:00		1.049764
05/11/2017 0:00		1.031841
03/12/2017 0:00		1.042663
02/09/2018 0:00	0.811046	0.811046
07/10/2018 0:00	1.012128	0.911587
04/11/2018 0:00	0.829227	0.884134

My expectation is to ignore the blanks while calculating the 4 months average,

For example,

Oct, Nov, and Dec 2017, which is blank should be ignored

For Sep 2018 the last 4 nonblank data points should be considered - ( July 2017+ Aug 2017 + Sep 2017 + Sep 2018)/4

Can anyone help, Please!!

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I can understand your problem, I think you can create a calculated column to help you to get the correct measure value, you can try this method:

1. Create a calculated column like this:

Index =

var _rank=RANKX(FILTER('Table',[Calc Measure]<>BLANK()),[All Date],,ASC,Dense)

return

IF([Calc Measure]=BLANK(),BLANK(),_rank)

This is the output of the calculated column:

2. Create a measure:

Moving Avg 4 months =

CALCULATE(AVERAGE([Calc Measure]),FILTER(ALL('Table'),[Index]<=MAX([Index])&&[Index]>=MAX([Index])-3&&[Index]<>0))

3. Then go to the table chart to place it like this:

And you can get the correct value as what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for suggesting this method. But the thing is Calc_Measure is not the column value. It's a calculated measure so Indexing doesn't work. The other solution posted by MFelix worked well.

Thanks for your help!!

[MFelix · Accepted solution]

Hi @Anonymous ,

You need to pick up the last 4 date values from your table instead of making the calculation based on the DATESINPERIOD:

Average_Last_4Months = 
AVERAGEX (
    TOPN (
        4,
        FILTER (
            SUMMARIZE ( ALL ( 'Table'[All Date] ), 'Table'[All Date], "@CalcMeasure", [Calc_Measure] ),
            [@CalcMeasure] <> BLANK ()
                && 'Table'[All Date] <= MAX ( 'Table'[All Date] )
        ),
        'Table'[All Date], DESC
    ),
    [@CalcMeasure]
)

Using the TOPN you will pickup the last rows you need for each month.

[Anonymous]

Thank you so much for providing the solution!! This is what I was looking for.

For the first 4 months, it didn't calculate properly. So I wrote an "if" condition and Index column to pick my old calculation if the index < 4 else the solution provided by you(Average_Last_4Months). It worked really well.

Once again thanks a ton.