cancel
Showing results for
Did you mean:
Helper I

## Trailing 4 Week Average

Hi

So I thought I had this solved but i don't 😞 I need 4 week trailing average of the number of orders. Below you can see the graph I am trying to create . The formula below looks like it works but its giving me the current week - 3 previous weeks average. If I change the -3 to -4 I get very different numbers as well.

Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-3 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])))

1 ACCEPTED SOLUTION
Super User

@BabyBinki821 , If you do not want this week

Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-4 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

or

Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-5 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

2 REPLIES 2
Super User

@BabyBinki821 , If you do not want this week

Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-4 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

or

Measure = calculate(AVERAGEX(values('DATE'[Week Rank]),Orders[Total Orders]),filter(all('DATE'),'DATE'[Week Rank]>=max('DATE'[Week Rank])-5 &&'DATE'[Week Rank]<=max('DATE'[Week Rank])-1  ))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Helper I

Thank you!!! It worked!

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.

#### Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors
Top Kudoed Authors