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 · Accepted solution]

@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]

Hello

Just a quick note to say this solution worked brilliantly. Thank you so much for your help on solving this issue. If i could i would buy you a thank you coffee!

Thanks 

Iain

[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.

[v-xinruzhu-msft]

Hi @iainh 

Did the solution AntrikshSharma  offered help you solve the problem, if it helps you can consider to accept it as solution so that more user can refet to, or if you other problems, you can offer  the information so that can provide more syggestion for you.

Best Regards!

Yolo Zhu

[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