Get data from previous row

[Anonymous]

Hi,

I have a table shown in pic 1. I have two projects and each of them have three phases: "Preparation", "Execution", and "Follow-up". Preparation phase is implemented between Gate 1 and Gate 2, Execution phase is implemented between Gate 2 and Gate 3, and Follow-up phase is implemented between Gate 3 and Gate 4. 

Gate 1 --> Preparation --> Gate 2 --> Execution --> Gate3 --> Follow-up --> Gate 4

In addition to my basic model in pic 1, I want to add project phases to my data with their start and end date (desired model in pic 3). I have added the conditional "Project Phase" column based on "Gates" column. If Gates column contains Gate 1 then Project Phase is Preparation, if If Gates column contains Gate 2 then Project Phase is Execution, and if If Gates column contains Gate 3 then Project Phase is Follow-up. This means that "Gate date" column will be the starting date of each phase. Now I want to add "Phase End" column which is basically the one row shifted up in "Gate Date". Can I get help with doing that? Then I want to maintain the same model in pic 3 as I add new project to my model as it is in pix 1. 

pic 1: Current state       pic 2: What I could dopic 3: Desired

Thanks in advance!

[Icey · Accepted solution]

Hi @Anonymous ,

You can try @edhans 's method. It works.

If you want to create a table using DAX, you can try this:

Table 2 =
VAR t1 =
    ADDCOLUMNS (
        'Table',
        "Project Phase", SWITCH (
            TRUE (),
            [Gates] = "Gate1", "Preparation",
            [Gates] = "Gate2", "Execution",
            [Gates] = "Gate3", "Follow-up"
        ),
        "Index", RANKX (
            ALLEXCEPT ( 'Table', 'Table'[Project ID] ),
            'Table'[Gates],
            ,
            ASC,
            DENSE
        )
    )
VAR t2 =
    ADDCOLUMNS (
        t1,
        "Phase End", CALCULATE (
            MAX ( 'Table'[Gate date] ),
            FILTER ( t1, [Index] = EARLIER ( [Index] ) + 1 )
        )
    )
RETURN
    FILTER (
        SELECTCOLUMNS (
            t2,
            "Project ID", [Project ID],
            "Project Phase", [Project Phase],
            "Phase Start", [Gate date],
            "Phase End", [Phase End]
        ),
        [Project Phase] <> BLANK ()
    )

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[edhans]

Hi,

In Power Query, create a BLANK query, then in the advanced editor, remove everything and paste this in:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1BCsAwCETRu7gO6IwJTc8Scv9rtDYNLS7cPOHPGEKHFHnOlErDKbNsZzi0Zvdwas9elzu+hzfbA6vTfxx9VzB79NuN2aN/KPH6vAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, Gate = _t, #"Gate Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Gate", Int64.Type}, {"Gate Date", type date}}),
    #"Added Phase" = Table.AddColumn(#"Changed Type", "Phase", each if [Gate] = 1 then "Preperation" else
if [Gate] = 2 then "Execution" else
if [Gate] = 3 then "Follow-up" else null, type text),
    #"Renamed Columns" = Table.RenameColumns(#"Added Phase",{{"Gate Date", "Phase Start"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    #"Added Index Increment" = Table.AddColumn(#"Added Index", "Index Increment", each [Index] - 1, Int64.Type),
    #"Merged to Itself" = Table.NestedJoin(#"Added Index Increment", {"Index", "Project ID"}, #"Added Index Increment", {"Index Increment", "Project ID"}, "Added Index Increment", JoinKind.LeftOuter),
    #"Expanded Added Index Increment" = Table.ExpandTableColumn(#"Merged to Itself", "Added Index Increment", {"Phase Start"}, {"Phase End"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Added Index Increment",{"Project ID", "Phase", "Phase Start", "Phase End"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Project ID", Order.Ascending}, {"Phase Start", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Phase End] <> null))
in
    #"Filtered Rows"

It will produce the results you want. Just load that into the Power BI DAX side and start makeing the visuals you want.

I did this by creating two indexes, one starting at 0, and the other being the previous index -1, then merged the table to itself using that offset Index, expanded the columns I needed, removed those that I didn't, and sorted so it looked like you had it.

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...