Trend spotting 02-25-2021 13:37 PM

[Greg_Deckler]

This one comes from @Route217 via this thread: Solved: Re: SPC and Shewart Analysis in Power BI - Microsoft Power BI Community

Basically, the desire was to be able to spot trends in a line graph so that the line would be a different color in the event, for example, six consecutive numbers were below the average or above the average. Normally, one might have to resort to SVG or R or Python to do this, but I found a DAX method. Basically, three measures, one for "lower" trends, one for "higher" trends and one for no trend. The lower trend measure is shown below as well as a measure for calculating the lower bound. The rest of the measures are in the PBIX file. There are two versions of the "no trends" measure, one that has breaks in the line and one without breaks in the line.

Lower Trend Measure = 
    VAR __Trend = [Trend]
    VAR __Current = MAX([Index])
    VAR __BaseTable = ALL('Table')
    VAR __Average = AVERAGEX(__BaseTable,[Detections])
    VAR __Detections = MAX([Detections])
    VAR __Table = 
        FILTER(
            ADDCOLUMNS(
                FILTER(__BaseTable,[Index] >= __Current - __Trend + 1 && [Index] <= __Current + __Trend - 1),
                "__Diff",[Detections] - __Average
            ),
            [__Diff] < 0
        )
    VAR __Table1 = 
        FILTER(
            ADDCOLUMNS(
                __Table,
                "__IndexDiff",[Index] - MAXX(FILTER(__Table,[Index]<EARLIER([Index])),[Index])
            ),
            [__IndexDiff] = 1 || [Index] = [__IndexDiff]
        )
    VAR __Table2 = 
        FILTER(
            ADDCOLUMNS(
                __Table1,
                "__IndexDiff2",[Index] - MAXX(FILTER(__Table1,[Index]<EARLIER([Index])),[Index])
            ),
            [__IndexDiff2] = 1 || [Index] = [__IndexDiff2]
        )
RETURN
    IF(COUNTROWS(__Table2) >= __Trend && __Detections < __Average,__Detections,BLANK())




Lower Bound = 
    VAR __BaseTable = ALL('Table')
    VAR __NoTrend = ADDCOLUMNS(__BaseTable,"__Measure",[No Trend Measure])
    VAR __LowerTrend = ADDCOLUMNS(__BaseTable,"__Measure",[Lower Trend Measure])
    VAR __UpperTrend = ADDCOLUMNS(__BaseTable,"__Measure",[Upper Trend Measure])
RETURN
    MINX(UNION(UNION(__NoTrend,__LowerTrend),__UpperTrend),[__Measure])

eyJrIjoiODJmZDhmZGMtNTUyNy00Y2JkLWE0MjQtZmU5YTA2ZGNkYWUwIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Route217]

Hi Greg

A question on the above trending chart would it be possible to idenitify 6 consective trending points either upwards or downwards in one line...this would be an addition to the above work same file..

[Greg_Deckler]

@Route217 Well, depends on what you mean by same line. The second page makes it look like a single line. Just a slight variation on the No Trend measure. The main issue with identifying a trend in a single line is that the line color for a line in a Line Chart cannot be conditionally formatted as far as I know. That is the reason I did it in multiple measures so I could get different line colors. Also, no way that I know of to dynamically change the marker either. Now, that being said, there is no reason that if you didn't want to use color as the indicator that you couldn't use a "flag" measure so that when you hovered over a data point it would tell you if it was part of a trend or not. That would definitely be possible. Would essentially mean just having to rewrite the "no trend" measure a bit. Is that what you are looking for or does it need to be a different color in the trends?

[Route217]

Hi Greg - Two Questions

1. In the Current Trend Analysis my dax is where VAR __Trend = 6 - if i change this to 8 - i am guess the dax well try and find 8 trend point in a row?? see image below

Lower Trend =

VAR __Trend = 6

VAR __Current = [Index]

VAR __Average = [Average Monthly Value]

VAR __Table =

FILTER(

ADDCOLUMNS(

FILTER(ALL('SPC Chart Data'),[Index] >= __Current - __Trend + 1 && [Index] <= __Current + __Trend - 1),

"__Diff",[Detections] - [Average Monthly Value]

),

[__Diff] < 0

)

VAR __Table1 =

FILTER(

ADDCOLUMNS(

__Table,

"__IndexDiff",[Index] - MAXX(FILTER(__Table,[Index]<EARLIER([Index])),[Index])

),

[__IndexDiff] = 1 || [Index] = [__IndexDiff]

)

VAR __Table2 =

FILTER(

ADDCOLUMNS(

__Table1,

"__IndexDiff2",[Index] - MAXX(FILTER(__Table1,[Index]<EARLIER([Index])),[Index])

),

[__IndexDiff2] = 1 || [Index] = [__IndexDiff2]

)

RETURN

IF(COUNTROWS(__Table2) >= __Trend && [Detections] < [Average Monthly Value],[Detections],BLANK())

2. When i mentinoed yesterday 6 consective point either falling and or raising - this is what i was on about... if this could be added to the current model as additonal Upper and lower bands?

[Greg_Deckler]

@Route217 Ah, right I focused on 6 data points above or below the average. And yes if you changed it to 8, it would look for 8 data points instead of 6. In the PBIX the "Trend" measure is set to 6 so you would want to change that. Let me see about a number of points increasing or decreasing in a row. Shouldn't be too difficult, just a different calculation for "__Diff" I think. I'll take a look and update the PBIX in this post and the post itself.

[Route217]

Hi Greg. Not sure if you had an opportunity to.look at the 6 consecutive data points increasing or decreasing in a row.

[Route217]

Morning, Greg - Hope you had a pleasent weekend. Not sure if managed to look at the 6 consective data points issues on the trending graph?

[Route217]

Hi Greg, if you could kindly add the two measure for 6 consecutive up and down as additionally calculated columns in the table, that would be fantastic. 

Thanks once again

[Greg_Deckler]

@Route217 - I updated the PBIX to include an Increasing Trend Measure and Decreasing Trend Measure. There is a third page that has these measures. I used a trend of 4 since my data didn't include 6 or 8 increasing or decreasing but you just have to modify the "Trend 2" measure to update from 4 to whatever number you want in your trend. Sorry this took a bit of time, got distracted by other things and I had to swap my thinking on how to identify and flag trends.

[Route217]

Many Thanks greg - much appericated for all your time and efforts. Thanks once again

[Route217]

Hi Greg, let me have a think and I'll reply back. Thanks 

[Route217]

Excellent post Greg...