Dynamically calculating consecutive data 'n' points

niharika0913

Here is a link to a sample model showing an example of what I believe you want to do.

My sample visual looks like this:

The key requirement to get this to work is to calculate the length of the current above-average run.

My model has a Data table with columns Date/Value/Type, related to a Date table.

The series of measures I created to get to the result are:

Your Measure is the base measure that is being plotted.

Your Measure = 
SUM ( Data[Value] )

Your Measure Average is the average that you want to compare Your Measure to on any given date. This should be constant over time. Can be defined however you like.

Your Measure Average for Display returns the average value only if Your Measure is nonblank (for use on the visual).

Your Measure Average = 
// Average of Your Measure over all dates
AVERAGEX ( ALL ( 'Date'[Date] ), [Your Measure] )

Your Measure Average for Display = 
// Only display average where Your Measure is nonblank
IF (
 NOT ( ISBLANK ( [Your Measure] ) ),
 [Your Measure Average]
)

Current Above Average Run Length is the most important measure, which calculates the current run length of above-average values visible in the visual. It basically starts from the current date and looks left and right to find the start/end points of the current run. This would have been difficult and probably unreadable without variables. Note: It returns blank if the current date is not within a run.

Current Above Average Run Length = 
IF (
    HASONEVALUE ( 'Date'[Date] ),
    VAR MeasureAverage = [Your Measure Average]
    VAR MeasureCurrent = [Your Measure]
    RETURN
        IF (
            MeasureCurrent > MeasureAverage,
            VAR CurrentDate =
                VALUES ( 'Date'[Date] )
            VAR VisibleDates =
                CALCULATETABLE ( SUMMARIZE ( Data, 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
            VAR VisibleDatesWithIndex =
                ADDCOLUMNS ( VisibleDates, "Index", RANKX ( VisibleDates, 'Date'[Date],, ASC ) )
            VAR DatesLeft =
                FILTER ( VisibleDatesWithIndex, 'Date'[Date] <= CurrentDate )
            VAR DatesRight =
                FILTER ( VisibleDatesWithIndex, 'Date'[Date] >= CurrentDate )
            VAR DatesLeftNotAboveAverage =
                FILTER ( DatesLeft, [Your Measure] <= MeasureAverage )
            VAR DatesRightNotAboveAverage =
                FILTER ( DatesRight, [Your Measure] <= MeasureAverage )
            VAR RunStart =
                IF (
                    ISEMPTY ( DatesLeftNotAboveAverage ),
                    MINX ( DatesLeft, [Index] ),
                    MAXX ( DatesLeftNotAboveAverage, [Index] ) + 1
                )
            VAR RunEnd =
                IF (
                    ISEMPTY ( DatesRightNotAboveAverage ),
                    MAXX ( DatesRight, [Index] ),
                    MINX ( DatesRightNotAboveAverage, [Index] ) - 1
                )
            RETURN
                RunEnd - RunStart
                    + 1
        )
)

Run Length Threshold harvests the threshold run length from a slicer selection, otherwise defaults to 2.

Run Length Threshold = 
SELECTEDVALUE( 'Run Length'[Run Length], 2 )
// Default Value = 2

Your Measure If Within Above Average Run just returns Your Measure for points within a qualifying run.

Your Measure If Within Above Average Run = 
IF (
    [Current Above Average Run Length] >= [Run Length Threshold],
    [Your Measure]
)

I'm not sure how 'optimal' this is performance-wise, but it's a starting point.

Regards,

Owen

Hi niharika0913,

Since you use legend field, you can't add multiple value fields to line chart. So, I'd like to suggest you write a measure to calculate average of each legend.

Sample measure:

AVG =
var current_Type=LASTNONBLANK(Table[Type],[Type])
return
AVERAGEX(FILTER(ALLSELECTED(Table),[Type]=current_Type),[Amount]) 

Regards,

Xiaoxin Sheng