Rolling Average not including months with no data

Try this dBrand 

 

3 Mo Rolling Average =
VAR varCurrentSalesMonth =
    MAX( Sales[Date] )
VAR varCurrentMonth =
    MAXX(
        FILTER(
            'Date',
            'Date'[Date] = varCurrentSalesMonth
        ),
        'Date'[Month Year Sort]
    )
VAR varValidDateRanges =
    FILTER(
        ALL( 'Date' ),
        'Date'[Month Year Sort] <= varCurrentMonth
            && 'Date'[Month Year Sort] >= varCurrentMonth - 2
    )
VAR varRolling3MonthsSales =
    CALCULATE(
        SUM( Sales[Sales] ),
        varValidDateRanges
    )
VAR varValidMonths =
    CALCULATE(
        DISTINCTCOUNT( 'Date'[Month Year Sort] ),
        FILTER(
            varValidDateRanges,
            CALCULATE(
                SUM( Sales[Sales] )
            )
                <> BLANK()
        )
    )
RETURN
    DIVIDE(
        varRolling3MonthsSales,
        varValidMonths,
        0
    )

 

It requires a date table. My Month Year Sort column is an integer in my date table that is YYYYMM, so 202001 for Jan, 202002 for Feb, etc. this gives me a good integer to move up/down by one month in the table by simpliy adding/subtracting 1. I also used it to count the months with data.

You can see there is no data for feb, so the march value of 17 is the (18+16)/2 months, March and Jan.
Same for August. (9+7)/2 = 8 because there is no July 2020 numbers. But Nov is (7+9+10)/3 = 8.666, shown as 9 due to how I have my data formated.

If you want to use my date table, here you go. Full instructions there for plopping that into Power Query. Just mark it as a date table once it loads.