Compare values from previous Rows on a sliced table

[Anonymous]

Hello Everyone, 

I'm currently trying to replicate an Excel File on Power BI and I'm facing a problem with one of the formulas that are in this file. 

Background : 

Data : about 1 000 000 rows

Column Description : 

• Type
• Date
• Value
• Max Value
• Out Max = Value>Max Value

What I've done : 

• I've created a slicer on Type to allow the end-user to select the type he wants and display the values.

What I want to do : 

I want to recreate the following  : Check for the 3Consecutive OutMax Column if the 3 Values that has been measured before my current value are OutMax = TRUE.

The ideal would be to be able to use a what if parameter to choose the number of consecutive value that I want to analyze (3,6,9... ) 

Thank you for your help  ! 

[AlB · Accepted solution]

Hi @Anonymous 

You need an additional colum to establish sequential order. I see that there are repeated dates so that doesn't help. If you don't have one, add an index column in the query editor (Add column - >Add index column)

Then create an additional calculated column. I'm assuming you want to check by type independently:

ConsecutiveOutMax =
VAR Last3_ =
    CALCULATETABLE (
        DISTINCT ( Table1[OutMax] ),
        Table1[Index] <= EARLIER ( Table1[Index] ),
        Table1[Index]
            > ( EARLIER ( Table1[Index] ) - 3 )
    )
RETURN
    IF ( FALSE () IN Last3_, FALSE (), TRUE () )