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.
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.
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.
Highlighted
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.
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.