Moving Average - 7 day

[Anonymous]

Hi all,

I have a DAX formula which helps me calculating a 7 day moving average, as follows:

Rolling AVG - 7 days = CALCULATE(SUM(val_summary_by_day_source_tv_region[vals_booked])/7,DATESINPERIOD('Calendar Reference'[Date_2],LASTDATE('Calendar Reference'[Date_2]),-7, DAY),ALLEXCEPT(val_source_ref_lookup,val_source_ref_lookup[val_business_category]))

The issue I'm having is that it calculates 7 days into the future, how can I edit the formula to only calculate up to yesterday?

Many thanks,

Jason

[v-lili6-msft · Accepted solution]

hi, @Anonymous

You could add a conditional in your formula as below:

Rolling AVG - 7 days =
CALCULATE (
    SUM ( val_summary_by_day_source_tv_region[vals_booked] ) / 7,
    DATESINPERIOD (
        'Calendar Reference'[Date_2],
        LASTDATE ( 'Calendar Reference'[Date_2] ),
        -7,
        DAY
    ),
    ALLEXCEPT (
        val_source_ref_lookup,
        val_source_ref_lookup[val_business_category]
    ),
    FILTER (
        ALL ( 'Calendar Reference' ),
        MAX ( 'Calendar Reference'[Date_2] ) < TODAY ()
    )
)

Result:

BeforeAfter

Best Regards,

Lin

[AlB]

Hi @Anonymous

Can you show how/where you are using the measure? I mean, is it on a matrix visual? If so, what are you placing in the rows of the matrix?