Forum Discussion
Moving Average - Visual Level Filters Applied
Hi, I got the moving average working as below:
MovingAverage2MonthAllRefs = CALCULATE (
AVERAGEX ( ALLSELECTED(Referrals), Referrals[Total] ),
DATESINPERIOD (
Referrals[Date],
LASTDATE ( Referrals[Date]),
-2,
MONTH
)
)
However when I try to apply a Visual Level Filter the Moving Average no longer calculates correctly.
| MmmYy | CCGName | Total | MovingAverage2MonthAllRefs |
| Aug-16 | NHS EAST STAFFORDSHIRE CCG | 2127 | 990.9376798 |
| Sep-16 | NHS EAST STAFFORDSHIRE CCG | 2341 | 1121.31327 |
| Oct-16 | NHS EAST STAFFORDSHIRE CCG | 2111 | 1016.064458 |
| Nov-16 | NHS EAST STAFFORDSHIRE CCG | 2317 | 1056.797127 |
| Dec-16 | NHS EAST STAFFORDSHIRE CCG | 1919 | 976.8295129 |
| Jan-17 | NHS EAST STAFFORDSHIRE CCG | 2055 | 1024.602256 |
| Feb-17 | NHS EAST STAFFORDSHIRE CCG | 2140 | 993.0767072 |
| Mar-17 | NHS EAST STAFFORDSHIRE CCG | 2665 | 1206.418858 |
| Apr-17 | NHS EAST STAFFORDSHIRE CCG | 1920 | 1043.4 |
| May-17 | NHS EAST STAFFORDSHIRE CCG | 2297 | 1184.435986 |
| Jun-17 | NHS EAST STAFFORDSHIRE CCG | 2203 | 1150.457726 |
| Jul-17 | NHS EAST STAFFORDSHIRE CCG | 2131 | 1002.993357 |
8 Replies
I just have 1 table (Referrals) which looks like the below
Date Specialty Name Ref Source Priority MmmYy VisitType E_Referral CCGName FY Month Total 01/04/2016 Acute Internal Medicine GP REFERRAL Routine Apr-16 Cons 0 NHS EAST STAFFORDSHIRE CCG 2016/17 April 2 01/04/2016 Acute Internal Medicine GP REFERRAL Routine Apr-16 Cons 0 NHS SOUTH EAST STAFFORDSHIRE AND SEISDON PENINSULA CCG 2016/17 April 1 01/04/2016 Audiology GP REFERRAL Routine Apr-16 NonCons 0 NHS CANNOCK CHASE CCG 2016/17 April 1 01/04/2016 Audiology GP REFERRAL Routine Apr-16 NonCons 0 NHS EAST LEICESTERSHIRE AND RUTLAND CCG 2016/17 April 1 01/04/2016 Audiology GP REFERRAL Routine Apr-16 NonCons 0 NHS EAST STAFFORDSHIRE CCG 2016/17 April 28 01/04/2016 Audiology GP REFERRAL Urgent Apr-16 NonCons 0 NHS SOUTH EAST STAFFORDSHIRE AND SEISDON PENINSULA CCG 2016/17 April 1 01/04/2016 Audiology GP REFERRAL FROM CAB Routine Apr-16 NonCons 1 NHS SOUTH EAST STAFFORDSHIRE AND SEISDON PENINSULA CCG 2016/17 April 1 01/04/2016 Audiology GP REFERRAL FROM CAB Routine Apr-16 NonCons 13 NHS SOUTHERN DERBYSHIRE CCG 2016/17 April 13 01/04/2016 Audiology REFERRAL FROM CAB UNKNOWN GP Routine Apr-16 NonCons 1 NHS SOUTH EAST STAFFORDSHIRE AND SEISDON PENINSULA CCG 2016/17 April 1 01/04/2016 Audiology REFERRAL FROM CAB UNKNOWN GP Routine Apr-16 NonCons 2 NHS SOUTHERN DERBYSHIRE CCG 2016/17 April 2 01/04/2016 Audiology REFERRAL FROM CAB UNKNOWN GP Urgent Apr-16 NonCons 1 NHS SOUTH EAST STAFFORDSHIRE AND SEISDON PENINSULA CCG 2016/17 April 1 I am trying to create a column chart which shows the Total Referrals per month - using the Total and MmmYy columns.
I have then created a calculated column using the code in my first message to calculate a moving 2 month average of Total.
However when I apply a Visual Level Filter to my column chart, e.g. where CCGName = ''NHS EAST STAFFORDSHIRE CCG", the moving average is not calculating correctly.
The results I am getting when applying this visual level filter are as follows:
MmmYy Total MovingAverage2MonthAllRefs Aug-16 2127 990.9376798 Sep-16 2341 1121.31327 Oct-16 2111 1016.064458 Nov-16 2317 1056.797127 Dec-16 1919 976.8295129 Jan-17 2055 1024.1 Feb-17 2140 992.6124357 Mar-17 2665 1211.669687 Apr-17 1920 1042.911517 May-17 2297 1184.435986 Jun-17 2203 1148.128031 Jul-17 2131 1005.195519 However I would expect the following results (based on using the moving 2 month average in Excel)
MmmYy Total MovingAverage2MonthAllRefs Aug-16 2127 Sep-16 2341 2234 Oct-16 2111 2226 Nov-16 2317 2214 Dec-16 1919 2118 Jan-17 2055 1987 Feb-17 2140 2097.5 Mar-17 2665 2402.5 Apr-17 1920 2292.5 May-17 2297 2108.5 Jun-17 2203 2250 Jul-17 2131 2167 Perhaps my original script to calculate the moving average is incorrect, however it seems to be cprrect when the visual level filter isnt applied.
Thanks for your help, I am a total novice with Power BI.
