3 (or n) months Rolling Average ignoring blanks and for future months

[ppdas2112]

Hello experts, 

Urgent request! Kindly help me! I have the following table.

• I require 3 (or any n) months Rolling Average, but ignoring the blank Spend rows while counting the number of months in denominator.
• For example, for 3 months rolling average in Mar-19, the calculation would be (248+176)/2 and not (248+176)/3.
• Also require the Rolling Average values for future months (till they become zero, which they eventually will)

Thanks in advance!

@amitchandak @johnt75 @tamerj1 @Jihwan_Kim @PaulDBrown @truptis @Ashish_Mathur @AlexisOlson @parry2k @Greg_Deckler @BA_Pete @mahoneypat @Vera_33 @Vijay_A_Verma @edhans @jennratten @smpa01 @Thingsclump  @SpartaBI @MFelix @speedramps @DataInsights @Shishir22 

[v-rongtiep-msft · Accepted solution]

Hi @ppdas2112 ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR before_three =
    EDATE ( MAX ( 'Table'[date] ), -2 )
VAR _sumvalue =
    CALCULATE (
        SUM ( 'Table'[Spend] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= before_three
                && 'Table'[date] <= MAX ( 'Table'[date] )
        )
    )
VAR _countrow =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= before_three
                && 'Table'[date] <= MAX ( 'Table'[date] )
        )
    )
RETURN
    _sumvalue / _countrow

If I have misunderstood your meaning, please provide more details with you desired output.(Preferably in picture form）

Best Regards

Community Support Team _ Polly

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

[ppdas2112]

Hi Ashish, that formula will take into consideration months without Spend value too

[Ashish_Mathur]

have you tried my suggested measure.  If it does not give the correct result, then share the download link of your PBI file and show the expected result.

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by Month number.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures:

Total spend = sum(Data[Spend])

Rolling 3 month spend = averagex(summarize(filter(calendar,datesbetween(calendar,edate(min(calendar[date]),-2),max(calendar[date]))),Calendar[Year],Calendar[Month name],"ABCD",[total spend]),[abcd])

If this does not work, then share the link of your PBI file.