Generate groups using previous rows

[Anonymous]

Hi there! I have the following table order by Date DESC:

Product	Type	Cycle	Date
Product 1	WO	NO	15/03/2015
Product 1	WO	YES	22/10/2015
Product 1	WO	NO	31/03/2016
Product 1	WO	YES	07/08/2017
Product 1	WO	YES	31/09/2017
Product 1	WO	YES	09/10/2017
Product 1	WO	YES	31/11/2017

I'm trying to create a new column with the following logic: If the previous row has "Cycle" value of "Yes" then the actual row will have the previous group + 1. The table has different products. Continuing with the example, I will have:  

Product	Type	Cycle	Date	Group
Product 1	WO	NO	15/03/2015	1
Product 1	WO	YES	22/10/2015	1
Product 1	WO	NO	31/03/2016	2
Product 1	WO	YES	07/08/2017	2
Product 1	WO	YES	31/09/2017	3
Product 1	WO	NO	09/10/2017	4
Product 1	WO	YES	31/11/2017	4

Thanks in advance!

Fernando

[v-deddai1-msft · Accepted solution]

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

[Anonymous · Accepted solution]

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!

[v-deddai1-msft · Accepted solution]

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

[FrankAT]

Hi @Anonymous 

are you looking for the following solution?

Measure = 
VAR _ActualDate =
    MIN ( 'Table'[Date] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] < _ActualDate )
    )
VAR _Group =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product] ),
            'Table'[Cycle] = "Yes"
                && 'Table'[Date] <= MIN ( 'Table'[Date] )
        )
    )
RETURN
    _Group

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Greg_Deckler]

@Anonymous - Do you have or can you add an Index? Otherwise, is "previous row" defined by the date column? If there are no previous rows is the group 1?

[lbendlin]

I think you meant ASCending sort.  Can you add an index column to your source data? If yes then you can use that when you add the group column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLlEwVNJRCvcHEn4gwtBU38BY38jA0FQpVgeLokjXYCBpZKRvaIBHFdgoY0NijDIw1zewAKkyx6cKZJYlHlVgC4EqIK4iZJShIVRVLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Type = _t, Cycle = _t, Date = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Group", 
each 1+List.Count(
    List.Select(
        Table.Column(
            Table.FirstN(#"Added Index",[Index])
            ,"Cycle")
        ,each _ ="YES")
    )
)
in
    #"Added Custom"

Note that your sample data is inconsistent.