Getting rolling 3 month average

[nleuck_101]

Hello All,

I'm having some trouble getting a rolling 3 month average to work properly. I've tried 5 different formulas and they return either 0 or blank. I have a Date table with the first date as 01/01/1989 and an ending date of 12/31/2027. My data table has EndOfMonth dates, that start on 01/31/2019 and end with 08/31/2022. I summed the data to a monthly total in SQL, to save time from loading all the rows.

Here is an example of the last formula I tried to use:

Any help would be greatly appreciated.

[DataInsights · Accepted solution]

@nleuck_101,

Have you tried this pattern?

https://www.daxpatterns.com/standard-time-related-calculations/#code61 

Sales AVG 3M :=
VAR Period3M =
    CALCULATETABLE (
        DATESINPERIOD (
            'Date'[Date],
            MAX ( 'Date'[Date] ),
            -3,
            MONTH
        ),
        'Date'[DateWithSales] = TRUE
    )
VAR FirstDayWithData =
    CALCULATE (
        MIN ( Sales[Order Date] ),
        REMOVEFILTERS ()
    )
VAR FirstDayInPeriod =
    MINX ( Period3M, 'Date'[Date] )
VAR Result =
    IF (
        FirstDayWithData <= FirstDayInPeriod,
        AVERAGEX (
            Period3M,
            [Sales Amount]
        )
    )
RETURN
    Result