Creating a XmR Control Chart in Power BI

[martyn_shields]

Hi All

I've looked through the forums and found some good posts on this topic but nothing that quite fits the challenge I currently have. I'm looking at replicating a XmR control chart dynamically with Power BI, using DAX I have got all but one element working.

The issue I have is I want to calculate the mean average of monthly actuals, not for the current financial year but for the previous financial year. I then want each financial year to replicate the same process insolation in the same column (averaging the monthly values for the previous financial year).

I have tried using the parallelperiod function along with datesinperiod but can't seem to achieve want I need, the raw DAX I need to run for the previous financial year is ;

Mean CALC =

CALCULATE (

AVERAGE (Incidents[Actual]),FILTER(ALLSELECTED(Incidents),Incidents[Actual]))

Here is a summarised copy of the current data structure. What I would like to achieve is in the [Average] column. For the example below the calculated figure for 2017-18 is 32.75 which is =average([Acutal] for Apr-16 to Mar-17).

Month	Actual	Financial Year	Average
Mar-16	48	2015-16
Apr-16	30	2016-17
May-16	33	2016-17
Jun-16	40	2016-17
Jul-16	38	2016-17
Aug-16	35	2016-17
Sep-16	32	2016-17
Oct-16	33	2016-17
Nov-16	30	2016-17
Dec-16	36	2016-17
Jan-17	34	2016-17
Feb-17	22	2016-17
Mar-17	30	2016-17
Apr-17	31	2017-18	32.75
May-17	39	2017-18	32.75
Jun-17	26	2017-18	32.75
Jul-17	20	2017-18	32.75
Aug-17	26	2017-18	32.75
Sep-17	21	2017-18	32.75
Oct-17	43	2017-18	32.75
Nov-17	26	2017-18	32.75
Dec-17	34	2017-18	32.75
Jan-18	34	2017-18	32.75
Feb-18	52	2017-18	32.75
Mar-18	29	2017-18	32.75
Apr-18	34	2018-19	31.75
May-18	49	2018-19	31.75
Jun-18	30	2018-19	31.75
Jul-18	26	2018-19	31.75
Aug-18	42	2018-19	31.75
Sep-18	28	2018-19	31.75
Oct-18	33	2018-19	31.75
Nov-18	34	2018-19	31.75
Dec-18	47	2018-19	31.75
Jan-19	32	2018-19	31.75
Feb-19	34	2018-19	31.75
Mar-19	6	2018-19	31.75

Is this possible? Any help would be much appreciated as all of the DAX is starting to blend into one 

Thanks

[v-piga-msft · Accepted solution]

Hi @martyn_shields ,

You could create the calculated columns below to get your desired output.

Column =
LEFT ( 'Table1'[Financial Year], 4 )
average_ =
CALCULATE (
    AVERAGE ( Table1[Actual] ),
    ALLEXCEPT ( Table1, 'Table1'[Financial Year] ),
    FILTER ( 'Table1', 'Table1'[Column] = EARLIER ( 'Table1'[Column] ) - 1 )
)

Here is the output.

Best  Regards,

Cherry

[martyn_shields]

Hi Cherry

Thanks for the quick response that works perfectly when calculating the average of a column is there any way of altering the DAX to work on a measure?

I have measure that is calculating the variance between the current period and the previous month, I also need to return the average of this measure using the same rules above. I tried to alter the DAX and use an AverageX instead of an Average but I received a circular dependency error.

The DAX for the variance is;

difference = abs(sum(Incidents[Actual]) - CALCULATE(sum(Incidents[Actual]),PARALLELPERIOD('Incidents'[Month],-1,MONTH)))

Any ideas please?

[CHR15F1T2]

Hi Martyn,  Just picked up this thread.  I'm having exactly same issue and wondered if you ever got it figured out?  I successfully created 'current month', 'previous month', 'monthly variance'.  When I then try to calculate the average of the variance for the selected period it doesn't make sense.  At this stage I have CALCULATE(AVERAGEX(,'Calendar table',[monthly variance]),ALLSELECTED('Calendar table')).  What it appears to be returning is the average for the entire dataset, NOT the filtered range.  NB - 'monthly variance' is a calculated measure which appears to be preventing me using alternative methods I've found online.