Calculating Difference from Previous Row

[Abi_W_981]

Hi, we have a table of data and results for each half term for a unique ID.

Example table below - what I want to be able to find is the half termly changes in grade value between half terms (excluding the first result)

I was thinking of using a PREVIOUSROW function but wanted to ensure that it only calculated the differences between the actual Unique IDs.

So the Half Termly Changes column is ideally what I am looking for - is anyone able to help with the best formula/calculation to use?

Thanks for your help in advance!

Unique ID	Grade Value	Half Term	Half Termly Changes
1234	4	Autumn 1
1234	7	Autumn 2	3
1234	5	Spring 1	-2
5678	12	Autumn 1
5678	14	Autumn 2	2
5678	14	Spring 1	0
91011	7	Autumn 1
91011	7	Autumn 2	0
91011	8	Spring 1	1

[ronrsnfld]

You can do this all in Power Query

• Group by Unique ID
• For each subgroup
  • Add an shifted (offset) Grade value column
    • (more efficient subtraction than using an Index column)
  • Subtract the Shifted Grade Value from the Gr ade Value
• Re-expand the table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQJhx9KS0tw8BUOlWB24uDlC3AhZ3BSIgwuKMvPSoepNzcwtgGKGRugGwSRM0E1ClkAxytLQwNAQ1W6cEkYoEhYoRsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Grade Value" = _t, #"Half Term" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", Int64.Type}, {"Grade Value", Int64.Type}, {"Half Term", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unique ID"}, {
        {"Half Termly Changes", (t)=>
            let 
                //add shifted grade value column
                //Then subtract from grade value
                #"Shifted Grade Value" = Table.FromColumns(
                                            Table.ToColumns(t)
                                            & {{null} & List.RemoveLastN(t[Grade Value])},
                                        type table[Unique ID=nullable number, Grade Value=nullable number, Half Term=nullable text, Shifted Grade Value=nullable number]),
                #"Change" = Table.AddColumn(#"Shifted Grade Value","Half Termly Change", each [Grade Value] - [Shifted Grade Value], type nullable number),
                #"Remove Shifted" = Table.RemoveColumns(#"Change",{"Shifted Grade Value"})
            in 
                #"Remove Shifted",
                type table [Unique ID=nullable number, Grade Value=nullable number, Half Term=nullable text, Half Termly Changes=nullable number]}}),
    #"Expanded Half Termly Changes" = Table.ExpandTableColumn(#"Grouped Rows", "Half Termly Changes", {"Grade Value", "Half Term", "Half Termly Change"})
in
    #"Expanded Half Termly Changes"

Results

[Abi_W_981]

Hi, thank you for your response to this.

As stated before I am fairly new to Power BI - I know how to Group by a column (and then re-expand it) but please could you explain how I would add a shifted Grade Value column? Is this done within the Group By window?

For the Subtraction, I am assuming this is a simple Column A - Column B calculation?

[ronrsnfld]

You need to do it in the advanced editor of the power query user interface. From Power BI select transform data. In the left hand column, select a new query from blank. From the home tab, select advanced editor. Then paste the code above into the window that opens in place of what is already there. You can then examine the code and the applied steps to better understand the process.

[HotChilli]

You can add an Index column in Power Query (via the interface) and then

A calculated column in DAX:

ColumnQ = 
var _ID = TableA[Unique ID]
var _Index = TableA[Index]
RETURN
IF (TableA[Half Term] <> "Autumn 1",
TableA[Grade Value] - 
CALCULATE(SUM(TableA[Grade Value]), 
  FILTER(TableA, TableA[Unique ID] = _ID && TableA[Index] = _Index - 1)),
0)

[Abi_W_981]

Hi, thank you for this

Just to check is this formula relying on Autumn 1 always being first for each individual Unique ID?

Something I have to mention is that for these results, the "first" result may not necessarily always be Autumn 1 and so the first result could be Autumn 2 or Spring 1 for example.

Would this affect the formula above?

[Abi_W_981]

Yes that is correct so there are 6 Half Terms per year - Autumn 1/Autumn 2, Spring 1/Spring 2 and Summer 1/Summer 2

For this year so far we only have up to Spring 1 grades (which is what I've used as the example) but as the year progresses that could get to a maximum of 6 records per Unique ID.

In PBI Query I have sorted the Unique IDs by the in year half terms so it should be in order yes. 

[HotChilli]

I wanted to know how the field is calculated because it looks like the 2nd row has a value of 23.

I think I see now that the second row is not 'Autumn 23' but 'Autumn 2' space '3'. Is that right?

--

In DAX there is no order in a table unless we apply some.  Will there always be 3 values for each ID?  Are they always ordered as in the example?  

[Abi_W_981]

The "Half Termly Changes" column is what I need assistance with - I believe it could be a PREVIOUSROW function but being fairly new to PBI I am still trying to understand all of the Power BI Language/Calculations

[HotChilli]

Please explain how the the new column is calculated