cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VictorR Frequent Visitor
Frequent Visitor

Rolling average with varying conditions

Hi

 

I have some sample data as I cannot share my actual data:

 

A table of visits by 6x different people from 3x different companies with their performance.

 

I am trying to display a visual such as a matrix/ table where if the end-user picks from a slicer a Person, the visual will show me the following:

 

  • That Person's latest performance
  • That Person's average performance based on the last 6 visits
  • That Person's Company's Average performance based on the last 3 months

 

I understand I will need to create two measures, but not sure how to get a rolling average for one column by the last 6 times it is in the data and another for the rolling average of a company by the last 3 months.

 

Would appreciate the help.

 

Data looks like below and actual sheet is available but not sure how to attach it to this post:

 

DateTimeNameCompanyPerformance
1-Jul-1818:18John SmithPST Ltd33.20
1-Jul-1821:05Gary PetersPST Ltd31.77
2-Jul-187:01Nicole George123 Solutions32.95
2-Jul-1822:34Clarisse Nathan123 Solutions29.63
3-Jul-187:24Terry EdwardsMPI Corp32.06
3-Jul-1811:42Cory BrewsterMPI Corp33.00
5-Jul-188:47John SmithPST Ltd31.13
5-Jul-1818:09Gary PetersPST Ltd33.64
6-Jul-181:30Nicole George123 Solutions32.85
6-Jul-188:22Clarisse Nathan123 Solutions36.92
6-Jul-1811:37Terry EdwardsMPI Corp28.43
7-Jul-181:45Cory BrewsterMPI Corp31.54
8-Jul-1818:23John SmithPST Ltd33.20
8-Jul-1822:24Gary PetersPST Ltd31.59
9-Jul-187:51Nicole George123 Solutions32.46
10-Jul-184:29Clarisse Nathan123 Solutions32.11
10-Jul-1810:01Terry EdwardsMPI Corp34.24
12-Jul-186:00Cory BrewsterMPI Corp25.08
12-Jul-1811:26John SmithPST Ltd27.31
12-Jul-1818:54Gary PetersPST Ltd36.41
13-Jul-185:40Nicole George123 Solutions38.06
13-Jul-188:11Clarisse Nathan123 Solutions33.64
13-Jul-1811:36Terry EdwardsMPI Corp28.29
14-Jul-182:23Cory BrewsterMPI Corp29.78
14-Jul-1818:17John SmithPST Ltd42.97
15-Jul-189:15Gary PetersPST Ltd27.36
15-Jul-1812:25Nicole George123 Solutions27.80
16-Jul-182:11Clarisse Nathan123 Solutions30.13
16-Jul-188:55Terry EdwardsMPI Corp30.15
17-Jul-188:42Cory BrewsterMPI Corp32.53
18-Jul-1821:39John SmithPST Ltd39.19
19-Jul-184:01Gary PetersPST Ltd31.60
19-Jul-1813:49Nicole George123 Solutions30.73
19-Jul-1818:40Clarisse Nathan123 Solutions37.95
20-Jul-187:46Terry EdwardsMPI Corp32.07
20-Jul-1811:06Cory BrewsterMPI Corp28.93
2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Rolling average with varying conditions

Hi @VictorR,

 

Please check the following steps.

 

1. Create a dimtime table and create relationship between fact table and date table.

dimtime = CALENDARAUTO()

2. Create the measures as below.

 

latest performance = var maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1))
return 
CALCULATE(MAX(Table1[Performance]),FILTER(Table1,Table1[Date]=maxdate))
average performance = AVERAGEX(
TOPN(6,Table1,Table1[Date],ASC),Table1[Performance])
Company's Average performance = CALCULATE(SUM(Table1[Performance]),DATEADD(dimtime[Date],-3,MONTH))/CALCULATE(COUNTROWS(Table1),DATEADD(dimtime[Date],-3,MONTH))

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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

View solution in original post

Community Support Team
Community Support Team

Re: Rolling average with varying conditions

Hi @VictorR,

 

Please try to open this file.

 

Regards,

Frank

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

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Rolling average with varying conditions

Hi @VictorR,

 

Please check the following steps.

 

1. Create a dimtime table and create relationship between fact table and date table.

dimtime = CALENDARAUTO()

2. Create the measures as below.

 

latest performance = var maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1))
return 
CALCULATE(MAX(Table1[Performance]),FILTER(Table1,Table1[Date]=maxdate))
average performance = AVERAGEX(
TOPN(6,Table1,Table1[Date],ASC),Table1[Performance])
Company's Average performance = CALCULATE(SUM(Table1[Performance]),DATEADD(dimtime[Date],-3,MONTH))/CALCULATE(COUNTROWS(Table1),DATEADD(dimtime[Date],-3,MONTH))

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

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

View solution in original post

VictorR Frequent Visitor
Frequent Visitor

Re: Rolling average with varying conditions

Hi Frank,

 

How are you narrowing down the Average in your Company's Average Performance measure by Company?

 

Also apologies I cannot open your .pbix file due to version differences.

 

My company's IT have set us all up on version: 2.56.5023.1021

Community Support Team
Community Support Team

Re: Rolling average with varying conditions

Hi @VictorR,

 

Please try to open this file.

 

Regards,

Frank

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

View solution in original post

Community Support Team
Community Support Team

Re: Rolling average with varying conditions

Hi @VictorR,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.

 

Regards,
Frank

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

Re: Rolling average with varying conditions

Hi @v-frfei-msft,

 

I was able to use your formula foir the last 6x visits and average performance. However, the Company's last three months' average is still not correct.

 

Your formula isn't referencing/ filtering down by company.

 

I think I will be better off doing this in SQL instead of within PowerBI and then import the query into the dashboard.

 

Thank you for your help and I will mark your response as a solution to close this case.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 286 members 3,160 guests
Please welcome our newest community members: