Duration of project and value over multiple years

[Mants]

Dear, 

I have a pipeline for multiple years. i need to present the value of multiyear projects per year i.e.

year 1: 20 days x 100 = 2000

year 2: 365 days x 100 = 36,500

year 3: 20 days x 100 = 2000

project total value: 40,500

How can i best split a single project duration (10/12/2023 - 20/1/2025) to days per year?

At the moment i have a multitude of today-eoy in days / if longer than 2023 then days in 2024 / etc....

[Anonymous]

This is how I would do it, but might be a bit processor heavy depending on the number of projects/length.

Link to PBIX 

Andrew

[AlienSx]

Hello, @Mants didn't have much time for testing but I hope you've got the idea. 

let
    projects = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKijKz0pNLok3MFTSUTIw0Tc00jcyMDIGccz1DU1BHFOlWB0khUYgOSN9A0OQHJhjDOWYKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, start = _t, end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(projects,{{"start", type date}, {"end", type date}}),
    fx_durations = (s as date, e as date) as list =>
        List.Generate(
            () => 
                [
                    current_EoY = Date.EndOfYear(s), 
                    next_EoY = Date.EndOfYear(Date.AddYears(s, 1)), 
                    year = Date.Year(s),
                    d_n = Duration.Days(List.Min({e, Date.EndOfYear(s)}) - s)
                ],
            (x) => x[year] <= Date.Year(e),
            (x) => 
                [
                    current_EoY = x[next_EoY],
                    next_EoY = Date.AddYears(x[next_EoY], 1),
                    year = Date.Year(x[next_EoY]),
                    d_n = Duration.Days(List.Min( {e, x[next_EoY]} ) - x[current_EoY])
                ],
            (x) => Record.SelectFields(x, {"year", "d_n"})
        ),
    duration_column = Table.AddColumn(#"Changed Type", "d_col", each fx_durations([start], [end])),
    expand_r_list = Table.ExpandListColumn(duration_column, "d_col"),
    expand_records = Table.ExpandRecordColumn(expand_r_list, "d_col", {"year", "d_n"}, {"year", "duration"})
in
    expand_records

[Mants]

Dear AlienSx, 

I must admit that im not well versed to fully grasp th esolution you proposed. i assume i need to create a new query and replace the Json part with the table name? (which i tried and it didnt seem to do anything)

If you could provide a little more advice that would be very much appreciated

[AlienSx]

Hello, @Mants exactly - replace json part with a table reference while keeping the name of the step ("projects"). This code expects your table to look like this.

[Anonymous]

Hi @Mants ,

Please try using Duartion.Days.

Best Regards,

Stephen Tao

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

[Mants]

Thank you, this is indeed what om doing at the moment. i was hopoing there would be an intelligence to distribute total days over the years of the project duration. not sure it would exist though.