cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling Average Using Index (NOT DATE)

Dear all,

I would like to ask , "What are examples of workable coding that I can try using if I need to perform rolling average of 30 weeks using the INDEX row and NOT Dates?" because based on my research that I have done so far, I mainly see people performing rolling average calculations using dates but not index which becomes unhelpful references for my case. Therefore, I decided to raise my question here instead.

All responses are welcomed.

Thank you for taking the time out to read this enquiry.

- Kaili

1 ACCEPTED SOLUTION
Super User

@Kaili So like:

``````Better RA =
VAR __Index = MAX('Table'[Index])
VAR __MinIndex = __Index - 30
VAR __Table = FILTER(ALLSELECTED('Table'),[Index] < __Index && [Index] >= __MinIndex)
RETURN
AVERAGEX(__Table,[Value])``````

Note, I pretty much just replaced [Date] with [Index]...

Better Running Total - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
9 REPLIES 9
Anonymous
Not applicable

Hello everyone,

New to the forum and first post. I was wondering which command to use to get the following results in power bi.

I am comparing scores to see if they are less than or greater than a certain range and thus helping with the decision/outome. The excel formula is working, how can i set up the same in Power bi. below is a screenshot showing the formula etc

Super User

@Anonymous Please post as a new topic.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Dear Mr @Greg_Deckler,

How can I modify the DAX formula to match with the calculated rolling average in my Excel data source file as there are differences between the data that I have and in the chart that I created in Power BI?

- Kaili

Super User

@Kaili Not the first clue. I don't have any data to compare one with the other so impossible to say.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Dear Mr @Greg_Deckler ,

here are the screenshots of my data (we are looking at row 276's total in Excel and the Rolling Average Sample 1 in Power BI). As you can see, the data does not match. Therefore, I would like to know if there are DAX amendments that I can make to ensure that this does not happen?

Yours sincerely,

Kaili

Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

@Greg_Deckler =SUM(M246:M276)/30

Super User

@Kaili So like:

``````Better RA =
VAR __Index = MAX('Table'[Index])
VAR __MinIndex = __Index - 30
VAR __Table = FILTER(ALLSELECTED('Table'),[Index] < __Index && [Index] >= __MinIndex)
RETURN
AVERAGEX(__Table,[Value])``````

Note, I pretty much just replaced [Date] with [Index]...

Better Running Total - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Dear Mr @Greg_Deckler,

Thank you so much for your suggested solution and I will give it a try.

- Kai Li

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors