Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to exclude/Ignore Blanks in Moving average calculation

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...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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.