Forum Discussion

CHill86's avatar
CHill86
Frequent Visitor
8 years ago

Moving Average - Visual Level Filters Applied

Hi, I got the moving average working as below:    MovingAverage2MonthAllRefs = CALCULATE (     AVERAGEX ( ALLSELECTED(Referrals), Referrals[Total] ),     DATESINPERIOD (         Referrals[Date],...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Instead of one table you should have at least two.

Create a Date table. Link it to your Referral table.
Try the following measure after the above steps.

MovingAverage2MonthAllRefs = CALCULATE (
    AVERAGEX ( Referrals, Referrals[Total] ),
    DATESINPERIOD (
        Dates[Date],
        LASTDATE ( Dates[Date]),
        -2,
        MONTH
    )
)

I changed the columns in the DatesPeriod function.

CHill86's avatar
CHill86
Frequent Visitor
8 years ago

thanks for your reply, but would you mind explaining why a dates table is needed when my source table already includes a date?

  • SivaMani's avatar
    SivaMani
    Resident Rockstar
    8 years ago

    Hi CHill86,

    Could you please try this,

     

    MovingAverage2MonthAllRefs = CALCULATE (
        AVERAGEX ( ALLSELECTED(Referrals), Referrals[Total] ),
        DATEADD(
            LASTDATE ( Referrals[Date]),
            -2,
            MONTH
        )
    )