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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
VictorR
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
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

View solution in original post

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 others find it more quickly.

View solution in original post

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

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

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 others find it more quickly.

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 others find it more quickly.

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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