Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Moving Average - 7 day

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
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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?