Dynamic number of calculated columns (Project Online schedule dates)

[iainh]

Hi all

I've been picking my brains over this problem for a while and I would like to get wider input from people that know more than me. 

With the Project Online data I've developed a way to capture a version history for each time a new schedule is published for certain critical tasks. I want to a way to dynamic calculate the slippage of these tasks for each published version.

For example on Project #1, the first published estimated finish date was 01/12/2024 but on the second published version it slips to 10/12/2024, therefore its 9 days slippage in the project schedule. Whereas, the Project #2 has 4 published versions and so on. 

Example data table:

Project #	Publish #1	Publish #2	Publish #3	Publish #4	Publish #n
1	01/01/2024	10/12/2024
2	01/02/2030	01/04/2030	01/04/2031	20/03/2031

(sorry for the table formatting, i don't know how to correct it) 

The problem is there is a variable number of published versions per the 300(ish) projects as they go through their project life cycle. Some have just 1 (those starting out), where as the older ones have more published versions. 

In short, is there a way to dynamically create columns to calculate the duration (in days) difference between Publish version #n and Publish version #n+1

Thanks for you're time and help on this

Iain

[ronrsnfld]

If all you want to show is the slippage between the last two published versions, then something like:

    #"Added Slippage" = Table.AddColumn(#"Changed Type","Slippage", (r)=>
        let 
            a = List.LastN(List.RemoveNulls(Record.FieldValues(r)),2)
        in 
            Duration.Days(a{1}-a{0}), Int64.Type)

[AntrikshSharma]

@iainh Does this work for you ?

let
    Source = Data,
    Unpivot = 
        Table.UnpivotOtherColumns ( Source, { "Project #" }, "Attribute", "Value" ),
    RemoveBlanks = 
        Table.SelectRows ( Unpivot, each ( [Value] <> " " ) ),
    ChangedType = 
        Table.TransformColumnTypes (
            RemoveBlanks,
            { { "Value", type date }, { "Project #", Int64.Type } },
            "en-GB"
        ),
    GroupedRows = 
        Table.Group (
            ChangedType,
            { "Project #" },
            {
                {
                    "Transform",
                    each
                        let
                            a = _,
                            b = Table.AddColumn (
                                a,
                                "n - 1",
                                ( x ) =>
                                    let
                                        PreviousDate = a[Value]{List.PositionOf ( a[Value], x[Value] ) - 1},
                                        CurrentDate = x[Value],
                                        Diff = try Duration.Days ( CurrentDate - PreviousDate ) otherwise 0
                                    in
                                        Diff
                            ),
                            c = Table.AddColumn (
                                b,
                                "n + 1",
                                ( x ) =>
                                    let
                                        NextDate = a[Value]{List.PositionOf ( a[Value], x[Value] ) + 1},
                                        CurrentDate = x[Value],
                                        Diff = try Duration.Days ( CurrentDate - NextDate ) otherwise 0
                                    in
                                        Number.Abs ( Diff )
                            )
                        in
                            c
                }
            }
        ),
    ExpandedTransform = 
        Table.ExpandTableColumn ( 
            GroupedRows, 
            "Transform", 
            {"Attribute", "Value", "n - 1", "n + 1"}, 
            {"Attribute", "Value", "n - 1", "n + 1"} 
        ),
    ChangedType2 = 
        Table.TransformColumnTypes ( 
            ExpandedTransform, 
            {
                {"Project #", Int64.Type}, 
                {"Attribute", type text}, 
                {"Value", type date}, 
                {"n - 1", Int64.Type}, 
                {"n + 1", Int64.Type}
            }
        )
in
    ChangedType2

Refer to the attached PBIX below.

[iainh]

Wow thanks for this. I'll have a look at it this evening and get back to you tomorrow if thats ok. 

[AntrikshSharma]

@iainh Here is a more refined approach in case there are duplicate publish dates, refer to Solution2

let
    Source = Data,
    Unpivot = 
        Table.UnpivotOtherColumns ( Source, { "Project #" }, "Publish#", "Published On" ),
    RemoveBlanks = 
        Table.SelectRows ( Unpivot, each [Published On] <> " " and [Published On] <> null ),
    ChangedType = 
        Table.TransformColumnTypes (
            RemoveBlanks,
            { { "Published On", type date }, { "Project #", Int64.Type } },
            "en-GB"
        ),
    GroupedRows = Table.Group (
        ChangedType,
        { "Project #" },
        {
            {
                "Transform",
                each
                    let
                        a = _,
                        b = a[Published On],
                        c = 
                            List.Accumulate (
                                b,
                                [ Dates = { List.First ( b ) }, vsPrevious = { 0 }, vsNext = { 0 } ],
                                ( s, c ) =>  
                                [
                                    Dates = s[Dates] & { c },
                                    vsPrevious = 
                                        s[vsPrevious] 
                                            & { Duration.Days ( c - List.Last ( s[Dates] ) ) },
                                    vsNext = 
                                        s[vsNext] 
                                            & { 
                                                Number.Abs ( 
                                                    Duration.Days ( List.Last ( s[Dates] ) - c ) 
                                                ) 
                                            }
                                ]
                            ),
                        d =
                            Record.TransformFields (
                                c,
                                {
                                    { "vsPrevious", List.Skip },
                                    { "vsNext", each List.Skip ( _ , 2) & {0} }
                                }
                            ),
                        e = { { "n - 1" } & d[vsPrevious] } & { { "n + 1" } & d[vsNext] },
                        f = Table.ToColumns ( Table.DemoteHeaders ( a ) ),
                        g = Table.PromoteHeaders ( Table.FromColumns ( f & e ) )
                    in
                        g
            }
        }
    ),
    CombineTables = 
        Table.Combine ( 
            GroupedRows[Transform], 
            type table [
                #"Project #" = Int64.Type,
                #"Publish#" = text, 
                Published On = date, 
                #"n - 1" = Int64.Type, 
                #"n + 1" = Int64.Type
            ] 
        )
in
    CombineTables

File attached below

[iainh]

I looked at this last night and came up with the same issue. Sir, not only are you a legend but you are a mind reader!

I'll look at this today and get back to you. Thank you so much for your time and help on this.

[lbendlin]

One of the very first methods to learn when coming from Excel to Power BI is to unpivot your data to bring it into a usable format.  In your case you will want to end up with a table with three columns

- project

- version

- date

Let the visuals do the re-pivoting for you.

[iainh]

Hello, thanks for your reply. 

Apolgises I don't think I explained myself clearly or I've misunderstood. In essence I want to end up with a table that includes the slippage duration for the respective publish versions. The published versions are ranked incermentally (from 1). This way I can track the slipage movement of the respective date/s in a table. This will then be used in by other dataflows for other purposes i.e., tracking schedule accuracy performance, ifdentifying top movers for business areas etc

Thanks

Iain