Dynamically calculating consecutive data 'n' points

kangsoonleong

If I understand you correctly, you are trying to calculate the number of "above average runs" within the selected time range, an interesting extension of the original question.

One way to do this would be to:

1. Create a measure Current Above Average Run Start Index that identifies, for a given date, the "start index" of the current run. This is used to uniquely identify each run.
2. Create a measure Count of Above Average Runs which counts the distinct values of the previous measure.

I'm not sure about performance for a large dataset, but here are some possible definitions. Note that the first measure is just a slight modification of the Current Above Average Run Length measure.

Sample model here

Current Above Average Run Start Index =
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
                IF ( RunEnd - RunStart + 1 >= [Run Length Threshold], RunStart )
        )
)

Count of Above Average Runs =
VAR VisibleDatesWithRunStartIndex =
    ADDCOLUMNS (
        ALLSELECTED ( 'Date'[Date] ),
        "RunStart", [Current Above Average Run Start Index]
    )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE ( VisibleDatesWithRunStartIndex, [RunStart] ),
            NOT ( ISBLANK ( [RunStart] ) )
        )
    )

At least these demonstrate that it can be done, but performance may need to be looked at for a large model.

Cheers

Owen