Find Previous Rating by Customer and Role

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVRNi8IwFPwroWchzUvtx1FFvOiu2MOyuB40LW6g2tImB//9JvZjY90FtYFA2sMMLzNvZrt1JozlZYKW8nRIS2fkTGarubreP97mG3UTgtUBF4j+cZ3d6F9I/BmvFleMi9VRGO9xjIdhrCH0cUjYQMCE5LISnO0zFBcp4/tM8LRCX9J1wUexLIrsoimuH2iWGw8NMNB25nAomfmaYCgZGA5Ew8kg0GSuDbKgnoyEQzTr/KSYtJr5Fshg+G50bPBXCF6nizDpTLBAR7GvXYjssOno0tbVjk/w/IwWl6KSpwaPlmsdyunsHk/CfpKegCuto/4ePAFv+wqM6D0F9++ceRzuYU8rF7yGhlo44hvwJD2Lco/RRh4PFzQp2TcXKROyVNZOJc8Sfj6idZknkonqKh+9zei46WL6G/iXOY0FrvvNszHpjfVhOyu1xRrWTUCtjmrUS2BPVOL1S2Y4q1n4Vq0C0i8JG7KSLkDWltVqrDpWH1OdK9K03O4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Name" = _t, Customer = _t, Role = _t, Date = _t, Rating = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Rating", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account Name", "Customer", "Role"}, {{"Rows", each let 
    #"Added Index" = Table.AddIndexColumn(Table.Sort(Table.SelectColumns(_,{"Date","Rating"}),{{"Date", Order.Ascending}}), "Index", 0, 1, Int64.Type)
    in Table.AddColumn(#"Added Index", "Change", each if [Index]>0 then [Rating]-#"Added Index"[Rating]{[Index]-1} else null,Int64.Type), type table [ Date=nullable date, Rating=nullable number, Change=nullable Int64.Type]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Date", "Rating", "Change"}, {"Date", "Rating", "Change"})
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

Here's a prettified version of the code:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "rVRNi8IwFPwroWchzUvtx1FFvOiu2MOyuB40LW6g2tImB//9JvZjY90FtYFA2sMMLzNvZrt1JozlZYKW8nRIS2fkTGarubreP97mG3UTgtUBF4j+cZ3d6F9I/BmvFleMi9VRGO9xjIdhrCH0cUjYQMCE5LISnO0zFBcp4/tM8LRCX9J1wUexLIrsoimuH2iWGw8NMNB25nAomfmaYCgZGA5Ew8kg0GSuDbKgnoyEQzTr/KSYtJr5Fshg+G50bPBXCF6nizDpTLBAR7GvXYjssOno0tbVjk/w/IwWl6KSpwaPlmsdyunsHk/CfpKegCuto/4ePAFv+wqM6D0F9++ceRzuYU8rF7yGhlo44hvwJD2Lco/RRh4PFzQp2TcXKROyVNZOJc8Sfj6idZknkonqKh+9zei46WL6G/iXOY0FrvvNszHpjfVhOyu1xRrWTUCtjmrUS2BPVOL1S2Y4q1n4Vq0C0i8JG7KSLkDWltVqrDpWH1OdK9K03O4H",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Account Name" = _t, Customer = _t, Role = _t, Date = _t, Rating = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"Date", type date}, {"Rating", Int64.Type}}
  ),
  #"Grouped Rows" = Table.Group(
    #"Changed Type",
    {"Account Name", "Customer", "Role"},
    {
      {
        "Rows",
        each
          let
            #"Added Index" = Table.AddIndexColumn(
              Table.Sort(Table.SelectColumns(_, {"Date", "Rating"}), {{"Date", Order.Ascending}}),
              "Index",
              0,
              1,
              Int64.Type
            )
          in
            Table.AddColumn(
              #"Added Index",
              "Change",
              each if [Index] > 0 then [Rating] - #"Added Index"[Rating]{[Index] - 1} else null,
              Int64.Type
            ),
        type table [Date = nullable date, Rating = nullable number, Change = nullable Int64.Type]
      }
    }
  ),
  #"Expanded Rows" = Table.ExpandTableColumn(
    #"Grouped Rows",
    "Rows",
    {"Date", "Rating", "Change"},
    {"Date", "Rating", "Change"}
  )
in
  #"Expanded Rows"

Hi Anonymous 

First of all, thanks to Ibendlin for his positive reply and the solution provided.

Allow me to add here a solution using DAX which I hope will help you.

1. Create a new column to add indexes for customer and role by date:

Index = 
    RANKX(
        FILTER(
            'Table',
            'Table'[Customer] = EARLIER('Table'[Customer]) &&
            'Table'[Role] = EARLIER('Table'[Role])
        ),
        'Table'[Date],
        ,
        ASC,
        DENSE
    )

2. Creates a new column to calculate the difference:

Difference = 
    VAR CurrentValue = 'Table'[Rating]
    VAR PreviousValue = 
        CALCULATE(
            MAX('Table'[Rating]),
            FILTER(
                'Table',
                'Table'[Customer] = EARLIER('Table'[Customer]) &&
                'Table'[Role] = EARLIER('Table'[Role]) &&
                'Table'[Index] = EARLIER('Table'[Index]) - 1
            )
        )
    VAR Diff = CurrentValue - PreviousValue
    RETURN
        IF(Diff = CurrentValue, BLANK(), Diff)

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

Hi lbendlin 

Thank you very much for your valueable suggestions! I will keep your input in mind for future enhancements. Thanks again for sharing.

Hi Anonymous 

According to Ibendlin's suggestion, using OFFSET() would be a much better solution, and the following optimized formulas are for your reference:

There is no need to create an index column, just a measure.

Diffence = 
VAR CurrentValue = MAX('Table'[Rating])
VAR PreviousValue = 
    CALCULATE(
        MAX('Table'[Rating]),
        OFFSET(
            -1,
            ORDERBY('Table'[Date], ASC),
            PARTITIONBY('Table'[Customer], 'Table'[Role])
        )
    )
RETURN
IF(ISBLANK(PreviousValue), BLANK(), CurrentValue - PreviousValue)

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