Rolling average with varying conditions

[VictorR]

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:

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

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft · Accepted solution]

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

[VictorR]

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 · Accepted solution]

Hi @VictorR,

Please try to open this file.

Regards,

Frank

[v-frfei-msft]

Hi @VictorR,

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

Regards,
Frank

[VictorR]

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.