Creating a process control chart that calculates average, LCL and UCL for the previous year

[HH_95]

Hi,

I have data that counts the number of jobs raised per week since 2019.

I would like to build a process control chart that calculates the average, UCL and LCL values for the previous year to the data that is being presented e.g: 2020 control values for the 2021 data and 2019 values for the 2020 data.

A slicer will be required to filter between current and previous year(s). The data source is an excel file that is usually updated daily.

This is what I've created so far but this calculates the control values from the same year as the data rather than the previous year but this is the format I would like.

I've even tried creating two different line charts, one for the control values and the other for the data, and then tried to overlay them but can't seem to get the scales to fit. This allows me to choose the year for both the control values and actual data. See below.

Week commencingCount of jobs

29 July 2019	15
05 August 2019	11
12 August 2019	34
19 August 2019	7
26 August 2019	4
02 September 2019	6
09 September 2019	15
16 September 2019	3
23 September 2019	6
30 September 2019	7
07 October 2019	3
14 October 2019	2
21 October 2019	4
28 October 2019	12
04 November 2019	4
18 November 2019	17
25 November 2019	8
02 December 2019	3
09 December 2019	5
16 December 2019	4
23 December 2019	1
30 December 2019	5
06 January 2020	10
13 January 2020	10
20 January 2020	5
27 January 2020	6
03 February 2020	10
10 February 2020	23
17 February 2020	30
24 February 2020	14
02 March 2020	12
09 March 2020	6
16 March 2020	12
23 March 2020	2
30 March 2020	12
06 April 2020	8
13 April 2020	4
20 April 2020	4
27 April 2020	10
04 May 2020	12
11 May 2020	13
18 May 2020	9
25 May 2020	13
01 June 2020	10
08 June 2020	11
15 June 2020	10
22 June 2020	24
29 June 2020	16
06 July 2020	17
13 July 2020	10
20 July 2020	11
27 July 2020	4
03 August 2020	11
10 August 2020	12
17 August 2020	12
24 August 2020	13
31 August 2020	9
07 September 2020	6
14 September 2020	18
21 September 2020	7
28 September 2020	19
05 October 2020	16
12 October 2020	9
19 October 2020	10
26 October 2020	9
02 November 2020	15
09 November 2020	11
16 November 2020	17
23 November 2020	10
30 November 2020	9
07 December 2020	7
14 December 2020	14
21 December 2020	11
28 December 2020	14
04 January 2021	11
11 January 2021	14
18 January 2021	22
25 January 2021	31
01 February 2021	23
08 February 2021	22
15 February 2021	23
22 February 2021	26
01 March 2021	13
08 March 2021	23
15 March 2021	31
22 March 2021	24
29 March 2021	23
05 April 2021	20
12 April 2021	25
19 April 2021	21
26 April 2021	22
03 May 2021	22
10 May 2021	21
17 May 2021	21
24 May 2021	32
31 May 2021	22
07 June 2021	32
14 June 2021	29
21 June 2021	12
28 June 2021	20
05 July 2021	20
12 July 2021	16
19 July 2021	15
26 July 2021	20
02 August 2021	32
09 August 2021	22
16 August 2021	24
23 August 2021	21
30 August 2021	25
06 September 2021	20
13 September 2021	31
20 September 2021	15
27 September 2021	35
04 October 2021	35
11 October 2021	25
18 October 2021	41
25 October 2021	28
01 November 2021	28
08 November 2021	26
BLANK	1046700
15 November 2021	29

Thanks

[amitchandak · Accepted solution]

@HH_95 , You need Max or Min based on some group by like week or Job ID,

Use first one when you date, with date table. Second you can use with year too

Last YTD Max = CALCULATE(Minx(Values(Job[ID]),[Week commencing]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Last Year Max = CALCULATE(Maxx(Values(Job[ID]),[Week commencing]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))