Moving Average - Visual Level Filters Applied

[CHill86]

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

[v-chuncz-msft]

@CHill86,

Please share us your simplified model and expected result.

[CHill86]

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.

[Anonymous]

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.

[CHill86]

thanks for your reply, but would you mind explaining why a dates table is needed when my source table already includes a date?

[SivaMani]

Hi @CHill86,

Could you please try this,

MovingAverage2MonthAllRefs = CALCULATE (
    AVERAGEX ( ALLSELECTED(Referrals), Referrals[Total] ),
    DATEADD(
        LASTDATE ( Referrals[Date]),
        -2,
        MONTH
    )
)

[Anonymous]

My answer was based on the assumption you were creating a measure.

In terms of the reason for the date table, you might want to read https://powerpivotpro.com/2011/11/the-ultimate-date-table/

[SivaMani]

@CHill86 Have you tried calculated measure instead of the calculated column?

[v-chuncz-msft]

@CHill86,

A simplied .pbix file would be better.