Help with identifying increase within a date range group

[JanoLehocky]

Good evening Power BI community,

Hoping someone can help me with this challenge that I would like to proper and elegant solution for. 

I am starting with data that looks like this:

Personnel no.	Personnel Name	Salary Band	Reason on Pay Record	Basic Pay Record Start Date
123	Jean-Luc Picard	12	Merit Review	03/01/2019
123	Jean-Luc Picard	13	Promotion	03/01/2020
123	Jean-Luc Picard	13	Other	10/19/2020
123	Jean-Luc Picard	13	Other	11/06/2020
234	Benjamin Sisco	10	Merit Review	03/01/2019
234	Benjamin Sisco	11	Merit Review	03/01/2020
234	Benjamin Sisco	11	Promotion	06/25/2021
3434534	Thomas Riker	10	Other	01/01/2019
3434534	Thomas Riker	10	Merit Review	03/01/2019
3434534	Thomas Riker	10	Other	01/01/2020
3434534	Thomas Riker	10	Merit Review	03/01/2020
3434534	Thomas Riker	10	Other	01/01/2021
666667	Travis Mayweather	8	Other	02/04/2019
666667	Travis Mayweather	9	Other	11/03/2019
666667	Travis Mayweather	10	Other	03/01/2020

The records above are entries from pay record entries. The ask was to identify:

1. Where/when the actual promotions are happening. (a promo is an increase in salary band)

2. Which promotions were on cycle (happening in March) vs off cycle (any other month than March)

Notice the following:

- each employees records are laid out in chronological order.  (See Basic Pay Record Start Date)

- the reason code is not consistent and therefore useless to try and harness... changes in salary band seem to happen for different reason codes

I would like to end up with something like this:

Personnel no.	Personnel Name	Salary Band	Reason on Pay Record	Basic Pay Record Start Date	PROMO STATUS	PROMO CYCLE
123	Jean-Luc Picard	12	Merit Review	43525
123	Jean-Luc Picard	13	Promotion	43891	Promotion	On Cycle
123	Jean-Luc Picard	13	Other	44123
123	Jean-Luc Picard	13	Other	44141
234	Benjamin Sisco	10	Merit Review	43525
234	Benjamin Sisco	11	Merit Review	43891	Promotion	On Cycle
234	Benjamin Sisco	11	Promotion	44372
3434534	Thomas Riker	10	Other	43466
3434534	Thomas Riker	10	Merit Review	43525
3434534	Thomas Riker	10	Other	43831
3434534	Thomas Riker	10	Merit Review	43891
3434534	Thomas Riker	10	Other	44197
666667	Travis Mayweather	8	Other	43500
666667	Travis Mayweather	9	Other	43772
666667	Travis Mayweather	10	Other	44013	Promotion	Off Cycle

I was actually planning at one point to have columns for each year but it turns out that some people get more than one promotion in the same year so that would not work so well.

Appreciate any insight.

Thank you inadvance PBI Community

~Jano

[JanoLehocky]

Good morning Jakinta!

Thank you for this solution!... BTW... you are correct... I missed one PROMO scenario... you got it right.

I am having trouble putting the code into power query. I am putting it into power query in excel.. does that make a difference?

This is what my code looks like:

let
Source = Excel.CurrentWorkbook(){[Name="All_HR_Promos"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Personnel no.", Int64.Type}, {"Personnel Name", type text}, {"Salary Band", Int64.Type}, {"Reason on Pay Record", type text}, {"Year", Int64.Type}, {"Basic Pay Record Start Date", type datetime}, {"Basic Pay Record End Date", type datetime}, {"Current Position Title", type text}, {"Current Position #", Int64.Type}, {"Current Manager Name", type text}, {"Current Managing Position", type text}, {"Current Org Level 4", type text}, {"Current Org Level 5", type text}, {"Current Org Level 6", type text}, {"Current Org Level 7", type text}, {"Current Org Level 8", type text}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Salary Band"})
[#"Personnel no." = _t, #"Personnel Name" = _t, #"Salary Band" = _t, #"Reason on Pay Record" = _t, #"Basic Pay Record Start Date" = _t]),
Grouped = Table.Group(Source, {"Personnel no."}, {{"Gr", each let t=_ in Table.AddColumn( Table.AddColumn( Table.AddIndexColumn(t, "i",-1,1), "PROMO STATUS", each try if Number.From([#"Salary Band"]) > Number.From(t[#"Salary Band"]{[i]}) then "Promotion" else "" otherwise ""), "PROMO CYCLE", each if [#"PROMO STATUS"]="" then "" else if Date.Month(Date.From([#"Basic Pay Record Start Date"])) =3 then "On Cycle" else "Off Cycle" ), type table }}),
Removed = Table.RemoveColumns(Grouped,{"Personnel no."}),
FINAL = Table.ExpandTableColumn(Removed, "Gr",
List.RemoveItems (Table.ColumnNames(Removed[Gr]{0}), {"i"}))
in
FINAL

I am getting an error -  Expression.SyntaxError: Token RightBracket expected.

It is showing that there is an error here:

[#"Personnel no." = _t, #"Personnel Name" = _t,

                              ^

Please advise. Thank you!

[Greg_Deckler]

@JanoLehocky Maybe:

PROMO STATUS Column =
  VAR __Previous = MAXX(FILTER('Table',[Personnel no.] = EARLIER([Personnel no.] && [Basic Pay Record Start Date] < EARLIER([Basic Pay Record Start Date])),[Basic Pay Record Start Date])
  VAR __PreviousBand = MAXX(FILTER('Table',[Personnel no.] = EARLIER([Personnel no.] && [Basic Pay Record Start Date] = __Pevious),[Salary Band])
RETURN
  IF(__PreviousBand > [Salary Band],"Promotion",BLANK())

PROMO CYCLE =
  IF(MONTH([Basic Pay Record Start])=3 && [PROMO STATUS] = "Promoted","On Cycle",BLANK())

[JanoLehocky]

@Greg_Deckler - Forgive me I am not entirely sure how to use your solution. Where do I paste this code in? Using the advanced editor? 

Thank you 

Jano

[Greg_Deckler]

@JanoLehocky No, I wasn't paying attention and gave you a DAX solution so you can't do that in Power Query, just as a DAX calculated column.

[JanoLehocky]

I just noticed the dates were not formatted right in the second table:

Personnel no.	Personnel Name	Salary Band	Reason on Pay Record	Basic Pay Record Start Date	PROMO STATUS	PROMO CYCLE
123	Jean-Luc Picard	12	Merit Review	03/01/2019
123	Jean-Luc Picard	13	Promotion	03/01/2020	Promotion	On Cycle
123	Jean-Luc Picard	13	Other	10/19/2020
123	Jean-Luc Picard	13	Other	11/06/2020
234	Benjamin Sisco	10	Merit Review	03/01/2019
234	Benjamin Sisco	11	Merit Review	03/01/2020	Promotion	On Cycle
234	Benjamin Sisco	11	Promotion	06/25/2021
3434534	Thomas Riker	10	Other	01/01/2019
3434534	Thomas Riker	10	Merit Review	03/01/2019
3434534	Thomas Riker	10	Other	01/01/2020
3434534	Thomas Riker	10	Merit Review	03/01/2020
3434534	Thomas Riker	10	Other	01/01/2021
666667	Travis Mayweather	8	Other	02/04/2019
666667	Travis Mayweather	9	Other	11/03/2019
666667	Travis Mayweather	10	Other	07/01/2020	Promotion	Off Cycle

[Jakinta]

Although I am not quite sure why your Desired Output table is not following the rule 

1. Where/when the actual promotions are happening. (a promo is an increase in salary band),

you can give a try to the code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndJdC4IwFAbgvzK8Nraz2Ye3XUZSVHfhxbBBK9xgWtG/bzOhZfiVF8IOezznPXg8BkBZEAYrwdVkfcvQVmbcnGwFqH0lwsgS7cRdioc9EoYJYEogDtKwg7ry1uhcl1Irz1HS7zblWRh3IBjisQYwmX0MZZEtLoW68FwqtJdFpqsv9yZrk9Aue3pCcyV2zqljUDEWsWha0cNZ57xAO3mt1+Dls438KbtRd8JxDetwfzUcZH8avtcyc8/cGcPvskAJfz4Er+8ufEcxiT7pOl3c+GPYQPc9qJcufQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personnel no." = _t, #"Personnel Name" = _t, #"Salary Band" = _t, #"Reason on Pay Record" = _t, #"Basic Pay Record Start Date" = _t]),
    Grouped = Table.Group(Source, {"Personnel no."}, {{"Gr", each let t=_ in Table.AddColumn( Table.AddColumn( Table.AddIndexColumn(t, "i",-1,1), "PROMO STATUS", each try if Number.From([#"Salary Band"]) > Number.From(t[#"Salary Band"]{[i]}) then "Promotion" else "" otherwise ""), "PROMO CYCLE", each if [#"PROMO STATUS"]="" then "" else if Date.Month(Date.From([#"Basic Pay Record Start Date"])) =3 then "On Cycle" else "Off Cycle" ), type table }}),
    Removed = Table.RemoveColumns(Grouped,{"Personnel no."}),
    FINAL = Table.ExpandTableColumn(Removed, "Gr", 
            List.RemoveItems (Table.ColumnNames(Removed[Gr]{0}), {"i"}))
in
    FINAL

Result:

[JanoLehocky · Accepted solution]

@Jakinta  - replied to you

[JanoLehocky]

Figured this out with a work colleague!  Thank you so much Jakinta!!! 

Cheers
Jano