DAX moving average not working

Question

I try to calculate moving average in DAX power bi. I use different codes, for example this.

    Moving AverageX 7B Days = 
    AVERAGEX (
    DATESINPERIOD( 
    sahkoInput[Date];
    LASTDATE ( sahkoInput[Date]);
    -7;
    DAY
    );
    sahkoInput[price]
    )

All codes give the same result - Moving AverageX 7B Days is equal to column "price". What went wrong and how to fix it?

Answer 1

Firstly, I would look to add a date/calendar table to your data model. This can be as simple as a list of consecutive dates from at least seven days before your first data point until after the end of when you expect your last one to be. The reason for this is that the date functions in DAX always work best when they have a table of consecutive dates to look at - you can get unpredictable results when your fact table doesn't have any data on a particular date.

Once you have added the date table, create a relationship to link the date column in your sahkoInput table to the date column in your date table.

Now, the following measure should work:

    Moving AverageX 7B Days = 
    CALCULATE (
               AVERAGE('sahkoInput'[Price]);
               DATESINPERIOD ('DateTable'[Date];
                               LASTDATE ('DateTable'[Date]);
                               -7;
                               DAY)
              )

Answer 2

Create a date table, this will dramatically improve performance and increase readability. You can do that by using the following DAX:

Min Date := MIN('sahkoInput'[Date])
Max Date := MAX('sahkoInput'[Date])

Dates :=
    VAR BaseCalendar =
        CALENDAR ( [Min Date], [Max date] )
    RETURN
        GENERATE (
            BaseCalendar,
            VAR BaseDate = [Date]
            VAR YearDate =
                YEAR ( BaseDate )
            VAR MonthNumber =
                MONTH ( BaseDate )
            RETURN
                ROW (
                    "Day", BaseDate,
                    "Year", YearDate,
                    "Month Number", MonthNumber,
                    "Month", FORMAT ( BaseDate, "mmmm" ),
                    "Year Month", FORMAT ( BaseDate, "mmm yy" )
                )
        )

Then referencing this date table you can create an average using the standard average function, like so:

Moving Average 7 Days :=
CALCULATE (
    AVERAGE ( 'sahkoInput'[Price] );
    KEEPFILTERS ( DATESINPERIOD ( 'Dates'[Date]; MAX ( 'Dates'[Date] ); -7; DAY ) )
)

I hope this helps!