Generate groups using previous rows

Hi Anonymous ,

You can create an index column in query editor:

And the use the following calculated column:

Column = VAR A = CALCULATE(DISTINCTCOUNT('Table'[Index]),FILTER('Table','Table'[Index]<= EARLIER('Table'[Index])&&'Table'[Cycle] = "YES")) RETURN IF('Table'[Cycle]= "NO",A+1,A)

For more details, please refer to the pbix file https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcJOk6WxpPJGgtsQubDfKaUBvgNo2B1qbezD1agnJv8lvw?e=3F4fbx

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Hi everyone!

Thanks all for your help. As lbendlin , Greg_Deckler  and v-deddai1-msft  said I needed an index into dataset.

I got a solution doing this (i think is very similar to the v-deddai1-msft solution):

Group = 
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            Table,
            Table[Date] < EARLIER ( Table[Date] )
                && Table[Product] = EARLIER (Table[Product] )
        ),
        [Date], DESC
    )
VAR PreviousCycle =
    MINX ( PreviousRow, [Cycle] )
VAR PreviousIndex =
    MINX ( PreviousRow, [Index] )
RETURN
    If(isblank(PreviousCycle),Table[Index], If (PreviousCycle = "YES", PreviousIndex+1,PreviousIndex))

Thanks everyone again!