Forum Discussion

LR's avatar
LR
Frequent Visitor
8 years ago
Solved

Dynamically calculating consecutive data 'n' points

 

Hi Team,

I study the variability of a production system. So I am trying to plot Statistical Process Charts in Power BI. This SPC charts are guided by some rules and I have to set up Nelson's eighth rule. This rule sais that "if 8 consecutive points are outside the range, then the data is out of control". The range of value is [ avg - stdev ; avg + stdev ].

On the picture you can see an exemple of this rule and the graph represents data :

 

 

 

 

Data change dynamically on the filter selection and are order by date and a type on the graph.
I didn't have any problem to build the interval with DAX formula. But I don't know how to compare values ​​at this range.

So my question is, how do you calculate a new measure that can find 'n' (can it be 1, 2, ..., 10) consecutives points that are outside this range?

I would like to indicate these values ​​on the graph above, and then count the number of times the rule is broken (ie the number of times or n consecutive points are out of range).

Please, could you help me to calculate a such DAX formula ?
Thanks.