Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kaili
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
Greg_Deckler
Super User
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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 

musavengana_0-1665056259167.png

 

@Anonymous Please post as a new topic.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Kaili
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?

 

Please advice. Thank you very much.

 

- Kaili

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

Further advice is much appreciated.

Yours sincerely,

Kaili

Screenshot (883).pngScreenshot (884).png

@Kaili What is your Excel formula for your G276 cell?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler =SUM(M246:M276)/30

Greg_Deckler
Super User
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Dear Mr @Greg_Deckler,

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

- Kai Li

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.