Date Range calculations of sub groups

[JanoLehocky]

Good evening Power BI Community!

Our SAP system puts out reports that detail time periods with the indicated salary band but there can be multiple records created at the same band level and there can be promotions and demotions. This makes it difficult to say how long they have been in a particular band.

Goal

Looking for output report to answer the following

1. Given the latest salary band, how long have they been in it

2. Over the course of their total employment, how much time was spent in total in each salary band?

I have sample data and a suggested output format here:

https://docs.google.com/spreadsheets/d/1Dr4RCnz5q7xq1vOnbieRYjQL9IOlTTdk/edit?usp=sharing&ouid=10053...

Open to a different way to display the final report...  as long as it shows the kind of data required.

Thank you in advance

~Jano.

[JanoLehocky]

Thanks Daniel!  I will test this out and let you know how it goes.

Thank you again!

J

[JanoLehocky]

You are using functions I have not heard of so I'll need to investigate each one. My goal is to be able to explain to someone how your code is doing the transformation. Really appreciate your knowledge here.

[wdx223_Daniel]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.TransformColumns(Source,{{"Start date",each Date.From(_,"en")},{"End date",each Date.From(_,"en")}}),
    Custom2 = #table(
                     Table.ColumnNames(Custom1)&{"Total Time (Years)","Time in Last Salary Band (Years)"},
                     List.TransformMany(
                                        Table.Group(
                                                    Custom1,
                                                    List.FirstN(Table.ColumnNames(Custom1),2),
                                                    {"n",
                                                     each List.Accumulate(
                                                                          Table.ToRows(_)&{{}},
                                                                          {},
                                                                          (x,y)=>if x={}
                                                                                 then {{},y,[]}
                                                                                 else if List.FirstN(x{1},3)=List.FirstN(y,3) and y{3}<=x{1}{4}+Duration.From(1)
                                                                                      then {x{0},List.FirstN(x{1},4)&{y{4}},x{2}}
                                                                                      else {x{0}&{x{1}},
                                                                                            y,
                                                                                            x{2}&Record.AddField(
                                                                                                                 [],
                                                                                                                 Text.From(x{1}{2}),
                                                                                                                 Record.FieldOrDefault(x{2},Text.From(x{1}{2}),0)+Duration.Days(List.Min({Date.From(DateTime.LocalNow()),x{1}{4}})-x{1}{3})/365
                                                                                                                )
                                                                                           }
                                                                         )
                                                    }
                                                   )[n],
                                        (x)=>List.Transform(x{0},each _&{Record.Field(x{2},Text.From(_{2})),Record.Field(x{2},Text.From(List.Last(x{0}){2}))}),
                                        (x,y)=>y
                                       )
                    )
in
    Custom2