cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Moving Average - 7 day

hi, @Jd018

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,
),
FILTER (
ALL ( 'Calendar Reference' ),
MAX ( 'Calendar Reference'[Date_2] ) < TODAY ()
)
)```

Result:

BeforeAfter

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User III

## Re: Moving Average - 7 day

Hi @Jd018

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?

Community Support

## Re: Moving Average - 7 day

hi, @Jd018

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,
),
FILTER (
ALL ( 'Calendar Reference' ),
MAX ( 'Calendar Reference'[Date_2] ) < TODAY ()
)
)```

Result:

BeforeAfter

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors