The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello
I am trying to create a measure or Calculated column which only returns values that below or above mean on 7 consective dates, for this table its the start of the week, I would only want to return the highlighted data for the measure/column, been looking for days without joy for this, I can do it in SQL but as this data source is SQL Server 2008 (upgrading soonish) then I lack the required Windows functions so need Dax which I am only starting to learn the last few weeks
I have only created the above mean column so far, but will be doing the below as well
I will also paste the data below from notepad, (above M measure not included in raw data)
Thanks
StartOfWeek ContractorName Jobs RFT Value Prev Value Moving Range Moving Range AVG Mean LCL UCL Above Mean
30/10/2023 00:00 Electrical Team 55 45 81.82% 76.14% 5.68% 5.76% 80.92% 65.59% 96.25% 81.82%
23/10/2023 00:00 Electrical Team 88 67 76.14% 84.34% 8.20% 5.76% 80.92% 65.59% 96.25%
16/10/2023 00:00 Electrical Team 83 70 84.34% 81.32% 3.02% 5.76% 80.92% 65.59% 96.25% 84.34%
09/10/2023 00:00 Electrical Team 91 74 81.32% 82.67% 1.35% 5.76% 80.92% 65.59% 96.25% 81.32%
02/10/2023 00:00 Electrical Team 75 62 82.67% 78.81% 3.85% 5.76% 80.92% 65.59% 96.25% 82.67%
25/09/2023 00:00 Electrical Team 118 93 78.81% 78.31% 0.50% 5.76% 80.92% 65.59% 96.25%
18/09/2023 00:00 Electrical Team 83 65 78.31% 80.00% 1.69% 5.76% 80.92% 65.59% 96.25%
11/09/2023 00:00 Electrical Team 75 60 80.00% 84.48% 4.48% 5.76% 80.92% 65.59% 96.25%
04/09/2023 00:00 Electrical Team 58 49 84.48% 84.75% 0.26% 5.76% 80.92% 65.59% 96.25% 84.48%
28/08/2023 00:00 Electrical Team 59 50 84.75% 72.58% 12.17% 5.76% 80.92% 65.59% 96.25% 84.75%
21/08/2023 00:00 Electrical Team 62 45 72.58% 78.95% 6.37% 5.76% 80.92% 65.59% 96.25%
14/08/2023 00:00 Electrical Team 76 60 78.95% 78.67% 0.28% 5.76% 80.92% 65.59% 96.25%
07/08/2023 00:00 Electrical Team 75 59 78.67% 83.72% 5.05% 5.76% 80.92% 65.59% 96.25%
31/07/2023 00:00 Electrical Team 86 72 83.72% 80.23% 3.49% 5.76% 80.92% 65.59% 96.25% 83.72%
24/07/2023 00:00 Electrical Team 86 69 80.23% 76.19% 4.04% 5.76% 80.92% 65.59% 96.25%
17/07/2023 00:00 Electrical Team 63 48 76.19% 81.58% 5.39% 5.76% 80.92% 65.59% 96.25%
10/07/2023 00:00 Electrical Team 76 62 81.58% 91.55% 9.97% 5.76% 80.92% 65.59% 96.25% 81.58%
03/07/2023 00:00 Electrical Team 71 65 91.55% 79.69% 11.86% 5.76% 80.92% 65.59% 96.25% 91.55%
26/06/2023 00:00 Electrical Team 64 51 79.69% 78.48% 1.21% 5.76% 80.92% 65.59% 96.25%
19/06/2023 00:00 Electrical Team 79 62 78.48% 79.31% 0.83% 5.76% 80.92% 65.59% 96.25%
12/06/2023 00:00 Electrical Team 87 69 79.31% 78.85% 0.46% 5.76% 80.92% 65.59% 96.25%
05/06/2023 00:00 Electrical Team 52 41 78.85% 83.67% 4.83% 5.76% 80.92% 65.59% 96.25%
29/05/2023 00:00 Electrical Team 49 41 83.67% 79.31% 4.36% 5.76% 80.92% 65.59% 96.25% 83.67%
22/05/2023 00:00 Electrical Team 58 46 79.31% 83.10% 3.79% 5.76% 80.92% 65.59% 96.25%
15/05/2023 00:00 Electrical Team 71 59 83.10% 75.81% 7.29% 5.76% 80.92% 65.59% 96.25% 83.10%
08/05/2023 00:00 Electrical Team 62 47 75.81% 80.56% 4.75% 5.76% 80.92% 65.59% 96.25%
01/05/2023 00:00 Electrical Team 72 58 80.56% 80.72% 0.17% 5.76% 80.92% 65.59% 96.25%
24/04/2023 00:00 Electrical Team 83 67 80.72% 86.08% 5.35% 5.76% 80.92% 65.59% 96.25%
17/04/2023 00:00 Electrical Team 79 68 86.08% 89.58% 3.51% 5.76% 80.92% 65.59% 96.25% 86.08%
10/04/2023 00:00 Electrical Team 48 43 89.58% 90.00% 0.42% 5.76% 80.92% 65.59% 96.25% 89.58%
03/04/2023 00:00 Electrical Team 70 63 90.00% 83.33% 6.67% 5.76% 80.92% 65.59% 96.25% 90.00%
27/03/2023 00:00 Electrical Team 66 55 83.33% 83.87% 0.54% 5.76% 80.92% 65.59% 96.25% 83.33%
20/03/2023 00:00 Electrical Team 93 78 83.87% 79.75% 4.12% 5.76% 80.92% 65.59% 96.25% 83.87%
13/03/2023 00:00 Electrical Team 79 63 79.75% 82.72% 2.97% 5.76% 80.92% 65.59% 96.25%
06/03/2023 00:00 Electrical Team 81 67 82.72% 75.79% 6.93% 5.76% 80.92% 65.59% 96.25% 82.72%
27/02/2023 00:00 Electrical Team 95 72 75.79% 77.91% 2.12% 5.76% 80.92% 65.59% 96.25%
20/02/2023 00:00 Electrical Team 86 67 77.91% 78.65% 0.74% 5.76% 80.92% 65.59% 96.25%
13/02/2023 00:00 Electrical Team 89 70 78.65% 82.11% 3.46% 5.76% 80.92% 65.59% 96.25%
06/02/2023 00:00 Electrical Team 123 101 82.11% 72.55% 9.56% 5.76% 80.92% 65.59% 96.25% 82.11%
30/01/2023 00:00 Electrical Team 102 74 72.55% 79.57% 7.02% 5.76% 80.92% 65.59% 96.25%
23/01/2023 00:00 Electrical Team 93 74 79.57% 71.13% 8.44% 5.76% 80.92% 65.59% 96.25%
16/01/2023 00:00 Electrical Team 97 69 71.13% 75.51% 4.38% 5.76% 80.92% 65.59% 96.25%
09/01/2023 00:00 Electrical Team 98 74 75.51% 73.49% 2.02% 5.76% 80.92% 65.59% 96.25%
02/01/2023 00:00 Electrical Team 83 61 73.49% 73.33% 0.16% 5.76% 80.92% 65.59% 96.25%
19/12/2022 00:00 Electrical Team 90 66 73.33% 75.59% 2.26% 5.76% 80.92% 65.59% 96.25%
12/12/2022 00:00 Electrical Team 127 96 75.59% 73.55% 2.04% 5.76% 80.92% 65.59% 96.25%
05/12/2022 00:00 Electrical Team 121 89 73.55% 77.88% 4.33% 5.76% 80.92% 65.59% 96.25%
28/11/2022 00:00 Electrical Team 104 81 77.88% 73.39% 4.49% 5.76% 80.92% 65.59% 96.25%
21/11/2022 00:00 Electrical Team 109 80 73.39% 81.13% 7.74% 5.76% 80.92% 65.59% 96.25%
14/11/2022 00:00 Electrical Team 106 86 81.13% 78.81% 2.32% 5.76% 80.92% 65.59% 96.25% 81.13%
07/11/2022 00:00 Electrical Team 118 93 78.81% 75.44% 3.37% 5.76% 80.92% 65.59% 96.25%
31/10/2022 00:00 Electrical Team 114 86 75.44% 81.00% 5.56% 5.76% 80.92% 65.59% 96.25%
24/10/2022 00:00 Electrical Team 100 81 81.00% 82.35% 1.35% 5.76% 80.92% 65.59% 96.25% 81.00%
17/10/2022 00:00 Electrical Team 85 70 82.35% 74.16% 8.20% 5.76% 80.92% 65.59% 96.25% 82.35%
10/10/2022 00:00 Electrical Team 89 66 74.16% 77.65% 3.49% 5.76% 80.92% 65.59% 96.25%
03/10/2022 00:00 Electrical Team 85 66 77.65% 77.91% 0.26% 5.76% 80.92% 65.59% 96.25%
26/09/2022 00:00 Electrical Team 86 67 77.91% 80.88% 2.98% 5.76% 80.92% 65.59% 96.25%
19/09/2022 00:00 Electrical Team 68 55 80.88% 85.37% 4.48% 5.76% 80.92% 65.59% 96.25%
12/09/2022 00:00 Electrical Team 82 70 85.37% 77.42% 7.95% 5.76% 80.92% 65.59% 96.25% 85.37%
05/09/2022 00:00 Electrical Team 93 72 77.42% 82.61% 5.19% 5.76% 80.92% 65.59% 96.25%
29/08/2022 00:00 Electrical Team 69 57 82.61% 77.14% 5.47% 5.76% 80.92% 65.59% 96.25% 82.61%
22/08/2022 00:00 Electrical Team 70 54 77.14% 83.10% 5.96% 5.76% 80.92% 65.59% 96.25%
15/08/2022 00:00 Electrical Team 71 59 83.10% 81.08% 2.02% 5.76% 80.92% 65.59% 96.25% 83.10%
08/08/2022 00:00 Electrical Team 74 60 81.08% 72.31% 8.77% 5.76% 80.92% 65.59% 96.25% 81.08%
01/08/2022 00:00 Electrical Team 65 47 72.31% 78.41% 6.10% 5.76% 80.92% 65.59% 96.25%
25/07/2022 00:00 Electrical Team 88 69 78.41% 83.05% 4.64% 5.76% 80.92% 65.59% 96.25%
18/07/2022 00:00 Electrical Team 59 49 83.05% 88.46% 5.41% 5.76% 80.92% 65.59% 96.25% 83.05%
11/07/2022 00:00 Electrical Team 78 69 88.46% 87.06% 1.40% 5.76% 80.92% 65.59% 96.25% 88.46%
04/07/2022 00:00 Electrical Team 85 74 87.06% 82.54% 4.52% 5.76% 80.92% 65.59% 96.25% 87.06%
27/06/2022 00:00 Electrical Team 63 52 82.54% 89.66% 7.12% 5.76% 80.92% 65.59% 96.25% 82.54%
20/06/2022 00:00 Electrical Team 58 52 89.66% 78.67% 10.99% 5.76% 80.92% 65.59% 96.25% 89.66%
13/06/2022 00:00 Electrical Team 75 59 78.67% 84.09% 5.42% 5.76% 80.92% 65.59% 96.25%
06/06/2022 00:00 Electrical Team 88 74 84.09% 70.59% 13.50% 5.76% 80.92% 65.59% 96.25% 84.09%
30/05/2022 00:00 Electrical Team 34 24 70.59% 76.32% 5.73% 5.76% 80.92% 65.59% 96.25%
23/05/2022 00:00 Electrical Team 76 58 76.32% 81.94% 5.63% 5.76% 80.92% 65.59% 96.25%
16/05/2022 00:00 Electrical Team 72 59 81.94% 72.94% 9.00% 5.76% 80.92% 65.59% 96.25% 81.94%
09/05/2022 00:00 Electrical Team 85 62 72.94% 85.00% 12.06% 5.76% 80.92% 65.59% 96.25%
02/05/2022 00:00 Electrical Team 60 51 85.00% 90.77% 5.77% 5.76% 80.92% 65.59% 96.25% 85.00%
25/04/2022 00:00 Electrical Team 65 59 90.77% 79.31% 11.46% 5.76% 80.92% 65.59% 96.25% 90.77%
18/04/2022 00:00 Electrical Team 58 46 79.31% 75.86% 3.45% 5.76% 80.92% 65.59% 96.25%
11/04/2022 00:00 Electrical Team 58 44 75.86% 80.28% 4.42% 5.76% 80.92% 65.59% 96.25%
04/04/2022 00:00 Electrical Team 71 57 80.28% 75.00% 5.28% 5.76% 80.92% 65.59% 96.25%
28/03/2022 00:00 Electrical Team 80 60 75.00% 85.33% 10.33% 5.76% 80.92% 65.59% 96.25%
21/03/2022 00:00 Electrical Team 75 64 85.33% 70.59% 14.75% 5.76% 80.92% 65.59% 96.25% 85.33%
14/03/2022 00:00 Electrical Team 68 48 70.59% 85.19% 14.60% 5.76% 80.92% 65.59% 96.25%
07/03/2022 00:00 Electrical Team 81 69 85.19% 86.60% 1.41% 5.76% 80.92% 65.59% 96.25% 85.19%
28/02/2022 00:00 Electrical Team 97 84 86.60% 92.86% 6.26% 5.76% 80.92% 65.59% 96.25% 86.60%
21/02/2022 00:00 Electrical Team 84 78 92.86% 75.24% 17.62% 5.76% 80.92% 65.59% 96.25% 92.86%
14/02/2022 00:00 Electrical Team 105 79 75.24% 88.37% 13.13% 5.76% 80.92% 65.59% 96.25%
07/02/2022 00:00 Electrical Team 86 76 88.37% 81.42% 6.96% 5.76% 80.92% 65.59% 96.25% 88.37%
31/01/2022 00:00 Electrical Team 113 92 81.42% 83.81% 2.39% 5.76% 80.92% 65.59% 96.25% 81.42%
24/01/2022 00:00 Electrical Team 105 88 83.81% 89.52% 5.71% 5.76% 80.92% 65.59% 96.25% 83.81%
17/01/2022 00:00 Electrical Team 105 94 89.52% 78.13% 11.40% 5.76% 80.92% 65.59% 96.25% 89.52%
10/01/2022 00:00 Electrical Team 96 75 78.13% 73.24% 4.89% 5.76% 80.92% 65.59% 96.25%
03/01/2022 00:00 Electrical Team 71 52 73.24% 89.55% 16.31% 5.76% 80.92% 65.59% 96.25%
20/12/2021 00:00 Electrical Team 67 60 89.55% 79.76% 9.79% 5.76% 80.92% 65.59% 96.25% 89.55%
13/12/2021 00:00 Electrical Team 84 67 79.76% 78.50% 1.26% 5.76% 80.92% 65.59% 96.25%
06/12/2021 00:00 Electrical Team 107 84 78.50% 82.91% 4.40% 5.76% 80.92% 65.59% 96.25%
29/11/2021 00:00 Electrical Team 117 97 82.91% 79.65% 3.26% 5.76% 80.92% 65.59% 96.25% 82.91%
22/11/2021 00:00 Electrical Team 113 90 79.65% 83.00% 3.35% 5.76% 80.92% 65.59% 96.25%
15/11/2021 00:00 Electrical Team 100 83 83.00% 81.82% 1.18% 5.76% 80.92% 65.59% 96.25% 83.00%
08/11/2021 00:00 Electrical Team 99 81 81.82% 88.79% 6.97% 5.76% 80.92% 65.59% 96.25% 81.82%
01/11/2021 00:00 Electrical Team 116 103 88.79% 84.21% 4.58% 5.76% 80.92% 65.59% 96.25% 88.79%
25/10/2021 00:00 Electrical Team 76 64 84.21% 83.87% 0.34% 5.76% 80.92% 65.59% 96.25% 84.21%
18/10/2021 00:00 Electrical Team 93 78 83.87% 75.76% 8.11% 5.76% 80.92% 65.59% 96.25% 83.87%
11/10/2021 00:00 Electrical Team 99 75 75.76% 83.53% 7.77% 5.76% 80.92% 65.59% 96.25%
04/10/2021 00:00 Electrical Team 85 71 83.53% 83.33% 0.20% 5.76% 80.92% 65.59% 96.25% 83.53%
27/09/2021 00:00 Electrical Team 84 70 83.33% 85.51% 2.17% 5.76% 80.92% 65.59% 96.25% 83.33%
20/09/2021 00:00 Electrical Team 69 59 85.51% 83.33% 2.17% 5.76% 80.92% 65.59% 96.25% 85.51%
13/09/2021 00:00 Electrical Team 78 65 83.33% 83.91% 0.57% 5.76% 80.92% 65.59% 96.25% 83.33%
06/09/2021 00:00 Electrical Team 87 73 83.91% 88.68% 4.77% 5.76% 80.92% 65.59% 96.25% 83.91%
30/08/2021 00:00 Electrical Team 53 47 88.68% 88.52% 0.15% 5.76% 80.92% 65.59% 96.25% 88.68%
23/08/2021 00:00 Electrical Team 61 54 88.52% 80.70% 7.82% 5.76% 80.92% 65.59% 96.25% 88.52%
16/08/2021 00:00 Electrical Team 57 46 80.70% 79.69% 1.01% 5.76% 80.92% 65.59% 96.25%
09/08/2021 00:00 Electrical Team 64 51 79.69% 84.62% 4.93% 5.76% 80.92% 65.59% 96.25%
02/08/2021 00:00 Electrical Team 78 66 84.62% 82.89% 1.72% 5.76% 80.92% 65.59% 96.25% 84.62%
26/07/2021 00:00 Electrical Team 76 63 82.89% 87.88% 4.98% 5.76% 80.92% 65.59% 96.25% 82.89%
19/07/2021 00:00 Electrical Team 66 58 87.88% 81.36% 6.52% 5.76% 80.92% 65.59% 96.25% 87.88%
12/07/2021 00:00 Electrical Team 59 48 81.36% 77.19% 4.16% 5.76% 80.92% 65.59% 96.25% 81.36%
05/07/2021 00:00 Electrical Team 57 44 77.19% 78.00% 0.81% 5.76% 80.92% 65.59% 96.25%
28/06/2021 00:00 Electrical Team 50 39 78.00% 83.58% 5.58% 5.76% 80.92% 65.59% 96.25%
21/06/2021 00:00 Electrical Team 67 56 83.58% 75.76% 7.82% 5.76% 80.92% 65.59% 96.25% 83.58%
14/06/2021 00:00 Electrical Team 66 50 75.76% 80.95% 5.19% 5.76% 80.92% 65.59% 96.25%
07/06/2021 00:00 Electrical Team 63 51 80.95% 82.46% 1.50% 5.76% 80.92% 65.59% 96.25% 80.95%
31/05/2021 00:00 Electrical Team 57 47 82.46% 85.33% 2.88% 5.76% 80.92% 65.59% 96.25% 82.46%
24/05/2021 00:00 Electrical Team 75 64 85.33% 84.34% 1.00% 5.76% 80.92% 65.59% 96.25% 85.33%
17/05/2021 00:00 Electrical Team 83 70 84.34% 78.05% 6.29% 5.76% 80.92% 65.59% 96.25% 84.34%
10/05/2021 00:00 Electrical Team 82 64 78.05% 73.85% 4.20% 5.76% 80.92% 65.59% 96.25%
03/05/2021 00:00 Electrical Team 65 48 73.85% 72.00% 1.85% 5.76% 80.92% 65.59% 96.25%
26/04/2021 00:00 Electrical Team 75 54 72.00% 80.26% 8.26% 5.76% 80.92% 65.59% 96.25%
19/04/2021 00:00 Electrical Team 76 61 80.26% 76.19% 4.07% 5.76% 80.92% 65.59% 96.25%
12/04/2021 00:00 Electrical Team 63 48 76.19% 82.46% 6.27% 5.76% 80.92% 65.59% 96.25%
05/04/2021 00:00 Electrical Team 57 47 82.46% 78.69% 3.77% 5.76% 80.92% 65.59% 96.25% 82.46%
29/03/2021 00:00 Electrical Team 61 48 78.69% 81.01% 2.32% 5.76% 80.92% 65.59% 96.25%
22/03/2021 00:00 Electrical Team 79 64 81.01% 83.33% 2.32% 5.76% 80.92% 65.59% 96.25% 81.01%
15/03/2021 00:00 Electrical Team 84 70 83.33% 78.31% 5.02% 5.76% 80.92% 65.59% 96.25% 83.33%
08/03/2021 00:00 Electrical Team 83 65 78.31% 84.52% 6.21% 5.76% 80.92% 65.59% 96.25%
01/03/2021 00:00 Electrical Team 84 71 84.52% 79.49% 5.04% 5.76% 80.92% 65.59% 96.25% 84.52%
22/02/2021 00:00 Electrical Team 78 62 79.49% 86.49% 7.00% 5.76% 80.92% 65.59% 96.25%
15/02/2021 00:00 Electrical Team 74 64 86.49% 81.43% 5.06% 5.76% 80.92% 65.59% 96.25% 86.49%
08/02/2021 00:00 Electrical Team 70 57 81.43% 77.78% 3.65% 5.76% 80.92% 65.59% 96.25% 81.43%
01/02/2021 00:00 Electrical Team 63 49 77.78% 68.33% 9.44% 5.76% 80.92% 65.59% 96.25%
25/01/2021 00:00 Electrical Team 60 41 68.33% 76.47% 8.14% 5.76% 80.92% 65.59% 96.25%
18/01/2021 00:00 Electrical Team 68 52 76.47% 88.89% 12.42% 5.76% 80.92% 65.59% 96.25%
11/01/2021 00:00 Electrical Team 63 56 88.89% 82.50% 6.39% 5.76% 80.92% 65.59% 96.25% 88.89%
04/01/2021 00:00 Electrical Team 80 66 82.50% 78.00% 4.50% 5.76% 80.92% 65.59% 96.25% 82.50%
21/12/2020 00:00 Electrical Team 50 39 78.00% 82.69% 4.69% 5.76% 80.92% 65.59% 96.25%
Thanks both ill look at these solutions today or tomorrow and post back with the results
Hi,
I am sure there is some syntactic sugar for this. However, here is my solution by foot. First I define my date index for the available dates:
Index =
VAR thisDate = 'YourTab'[Date]
RETURN
COUNTROWS ( FILTER ( ALL ( 'YourTab' ), 'YourTab'[YourTab] < thisDate ) ) + 1
This ensures that, if a week is missing I will be taking the last lookback weeks for checking the concecutive above mean criterion.
Now the next calculted column checks if the last 5 consecutive values are above the mean:
IsQualified =
VAR thisIndex = 'YourTab'[Index]
VAR lookbackInNumberDates = 5
RETURN
CALCULATE (
SUM ( 'YourTab'[AboveMean] ),
FILTER (
'YourTab',
'YourTab'[Index] <= thisIndex
&& 'Date'[YourTab] > ( thisIndex - lookbackInNumberDates )
)
) == lookbackInNumberDates
Basically, with the 0 1 column for above the mean you have been already have way there: if you always select the last n rows before you current date (or whatever your selection is), you'd expect the sum of the above mean column filtered in this window to be exactly n. You can play with the condition in the filter to define an arbitrary business rule.
Best regards
Christian
I could get that to work quite right sorry, I used this in the end, I created measure to return the previous 8 (Board now want 8 points) values onto the row then used this measure
Note it only highlights the last of the 8 points above the mean, but it works for us
Thanks
Hi @matg07 ,
You can create a calculated column as below to get it:
Above Flag = IF ( NOT ( ISBLANK ( [Above Mean] ) ), 1, BLANK () )
If the above one can't help you get the expected result, please explain your requirement with the specific examples base on the above sample data. It would be helpful to find the solution. Thank you.
It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |