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 performanceThat Person's average performance based on the last 6 visitsThat 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: DateTimeNameCompanyPerformance1-Jul-1818:18John SmithPST Ltd33.201-Jul-1821:05Gary PetersPST Ltd31.772-Jul-187:01Nicole George123 Solutions32.952-Jul-1822:34Clarisse Nathan123 Solutions29.633-Jul-187:24Terry EdwardsMPI Corp32.063-Jul-1811:42Cory BrewsterMPI Corp33.005-Jul-188:47John SmithPST Ltd31.135-Jul-1818:09Gary PetersPST Ltd33.646-Jul-181:30Nicole George123 Solutions32.856-Jul-188:22Clarisse Nathan123 Solutions36.926-Jul-1811:37Terry EdwardsMPI Corp28.437-Jul-181:45Cory BrewsterMPI Corp31.548-Jul-1818:23John SmithPST Ltd33.208-Jul-1822:24Gary PetersPST Ltd31.599-Jul-187:51Nicole George123 Solutions32.4610-Jul-184:29Clarisse Nathan123 Solutions32.1110-Jul-1810:01Terry EdwardsMPI Corp34.2412-Jul-186:00Cory BrewsterMPI Corp25.0812-Jul-1811:26John SmithPST Ltd27.3112-Jul-1818:54Gary PetersPST Ltd36.4113-Jul-185:40Nicole George123 Solutions38.0613-Jul-188:11Clarisse Nathan123 Solutions33.6413-Jul-1811:36Terry EdwardsMPI Corp28.2914-Jul-182:23Cory BrewsterMPI Corp29.7814-Jul-1818:17John SmithPST Ltd42.9715-Jul-189:15Gary PetersPST Ltd27.3615-Jul-1812:25Nicole George123 Solutions27.8016-Jul-182:11Clarisse Nathan123 Solutions30.1316-Jul-188:55Terry EdwardsMPI Corp30.1517-Jul-188:42Cory BrewsterMPI Corp32.5318-Jul-1821:39John SmithPST Ltd39.1919-Jul-184:01Gary PetersPST Ltd31.6019-Jul-1813:49Nicole George123 Solutions30.7319-Jul-1818:40Clarisse Nathan123 Solutions37.9520-Jul-187:46Terry EdwardsMPI Corp32.0720-Jul-1811:06Cory BrewsterMPI Corp28.93

Frequent Visitor

8 years ago

Solved

Rolling average with varying conditions

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:

Date	Time	Name	Company	Performance
1-Jul-18	18:18	John Smith	PST Ltd	33.20
1-Jul-18	21:05	Gary Peters	PST Ltd	31.77
2-Jul-18	7:01	Nicole George	123 Solutions	32.95
2-Jul-18	22:34	Clarisse Nathan	123 Solutions	29.63
3-Jul-18	7:24	Terry Edwards	MPI Corp	32.06
3-Jul-18	11:42	Cory Brewster	MPI Corp	33.00
5-Jul-18	8:47	John Smith	PST Ltd	31.13
5-Jul-18	18:09	Gary Peters	PST Ltd	33.64
6-Jul-18	1:30	Nicole George	123 Solutions	32.85
6-Jul-18	8:22	Clarisse Nathan	123 Solutions	36.92
6-Jul-18	11:37	Terry Edwards	MPI Corp	28.43
7-Jul-18	1:45	Cory Brewster	MPI Corp	31.54
8-Jul-18	18:23	John Smith	PST Ltd	33.20
8-Jul-18	22:24	Gary Peters	PST Ltd	31.59
9-Jul-18	7:51	Nicole George	123 Solutions	32.46
10-Jul-18	4:29	Clarisse Nathan	123 Solutions	32.11
10-Jul-18	10:01	Terry Edwards	MPI Corp	34.24
12-Jul-18	6:00	Cory Brewster	MPI Corp	25.08
12-Jul-18	11:26	John Smith	PST Ltd	27.31
12-Jul-18	18:54	Gary Peters	PST Ltd	36.41
13-Jul-18	5:40	Nicole George	123 Solutions	38.06
13-Jul-18	8:11	Clarisse Nathan	123 Solutions	33.64
13-Jul-18	11:36	Terry Edwards	MPI Corp	28.29
14-Jul-18	2:23	Cory Brewster	MPI Corp	29.78
14-Jul-18	18:17	John Smith	PST Ltd	42.97
15-Jul-18	9:15	Gary Peters	PST Ltd	27.36
15-Jul-18	12:25	Nicole George	123 Solutions	27.80
16-Jul-18	2:11	Clarisse Nathan	123 Solutions	30.13
16-Jul-18	8:55	Terry Edwards	MPI Corp	30.15
17-Jul-18	8:42	Cory Brewster	MPI Corp	32.53
18-Jul-18	21:39	John Smith	PST Ltd	39.19
19-Jul-18	4:01	Gary Peters	PST Ltd	31.60
19-Jul-18	13:49	Nicole George	123 Solutions	30.73
19-Jul-18	18:40	Clarisse Nathan	123 Solutions	37.95
20-Jul-18	7:46	Terry Edwards	MPI Corp	32.07
20-Jul-18	11:06	Cory Brewster	MPI Corp	28.93

Need Help

v-frfei-msft

8 years ago

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))

For more details, please check the pbix as attached.

Regards,

Frank

Rolling average with varying conditions.pbix337 KB

v-frfei-msft
8 years ago
Hi VictorR,

Please try to open this file.

Regards,

Frank
new file with mar version.pbix348 KB

5 Replies

v-frfei-msft
Community Support
8 years ago
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))

For more details, please check the pbix as attached.

Regards,

Frank
Rolling average with varying conditions.pbix337 KB
- VictorR
  Frequent Visitor
  8 years ago
  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
  - v-frfei-msft
    Community Support
    8 years ago
    Hi VictorR,
    
    Please try to open this file.
    
    Regards,
    
    Frank
    new file with mar version.pbix348 KB

Forum Discussion

Rolling average with varying conditions

5 Replies

Related content

Conditional Rolling Average

Rolling 12M Average measure

3 Day Rolling Average - DAX

Rolling accumulation

Rolling accumulation