Forum Discussion
Moving Average - Visual Level Filters Applied
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.
Instead of one table you should have at least two.
Create a Date table. Link it to your Referral table.
Try the following measure after the above steps.MovingAverage2MonthAllRefs = CALCULATE ( AVERAGEX ( Referrals, Referrals[Total] ), DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date]), -2, MONTH ) )I changed the columns in the DatesPeriod function.
