Create range based on previous row

[Anonymous]

I have this database with columns A and B and I would like to create columns C and D (START, END), in DAX or Power Query.

Column D would just repeat column B, but column C should be ordered and create the range with the previous value of each Item.

Is this possible to be created?

[tamerj1 · Accepted solution]

Hi @Anonymous 
If you wish to use DAX you may try

Start =
MAXX (
    FILTER (
        CALCULATETABLE (
            VALUES ( 'Table'[VALUE] ),
            ALLEXCEPT ( 'Table', 'Table'[ITEM] )
        ),
        'Table'[Value] < EARLIER ( 'Table'[Value] )
    ),
    'Table'[Value]
)

[giammariam]

@Anonymous, I ended up going with a Power Query implementation. I did it pretty hastily so if you need me to go back and annotate the steps, let me know. Essentially though, if the column names are the same, you would just need to update the source (first step) to use your data. 

Here are the steps I took using M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK21DE1VYrVgfBMDHTMDBA8Cx1jiJwTkGegY2gA5xjqmCJzzIGcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Value = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"Item", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Item", type text}, {"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {{"Count", each _, type table [Item=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "i", each Table.AddIndexColumn([Count], "Index", 1)),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom", "i", {"Value", "Index"}, {"Value", "Index Grouped"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Index", "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Count"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Lag Item", each try List.Range(Source[Item], [Index]-1, 1) otherwise null),
    #"Expanded Lead Item" = Table.ExpandListColumn(#"Added Custom2", "Lag Item"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Lead Item", "Lag Value", each try List.Range(Source[Value], [Index]-1, 1) otherwise null),
    #"Expanded Lead Value" = Table.ExpandListColumn(#"Added Custom3", "Lag Value"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Lead Value", "START", each if [Index Grouped] = 1 then 0 else if [Item] = [Lag Item] then [Lag Value] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index Grouped", "Index", "Lag Item", "Lag Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"START", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type1", "END", each [Value])
in
    #"Added Custom4"

Here is a .pbix that acts as an example

If this gets you what you need, please mark this as the solution.