Creating a Percent Change Column in Power Query

[ACraig08]

I want to add a column for percent change between Fiscal Years within each ITEM_DESCRIPTION. I have tried so many things and can't figure this out. I am getting so annoyed. I can't believe Power Query doesn't have a Previous Row function built in by!! Let alone a Percent Change function that allows you to choose the parameters. 

**The Percent Change column in bold is what I am trying to achieve, I just put it there so you could see what I was trying to do.

In Power Query I used the Group By and Index functions to get the index to restart for each ITEM_DESCRIPTION, although I don't know if that was the right thing to do or even helpful.

ITEM_DESCRIPTION	Fiscal Year	Index	Avg. Awarded PPU	Percent Change
CONCRETE FOOTING (MONOTUBE)	2021	1	4429	0
CONCRETE FOOTING (MONOTUBE)	2022	2	4396	-33
CONCRETE FOOTING (MONOTUBE)	2023	3	2209	-2187
CONCRETE FOOTING (MONOTUBE)	2024	4	1270	-939
CONCRETE FOOTING (MONOTUBE)	2025	5	1172	-98
CONCRETE FOOTING (OVERHEAD SHAFT)	2022	2	1504	0
CONCRETE FOOTING (OVERHEAD SHAFT)	2023	3	1162	-342
CONCRETE FOOTING (OVERHEAD SHAFT)	2024	4	1020	-142
CONCRETE FOOTING (OVERHEAD SHAFT)	2025	5	2608	1588
EXTRUDED PANEL	2021	1	1193	0
EXTRUDED PANEL	2022	2	3714	2521
EXTRUDED PANEL	2023	3	2378	-1336
EXTRUDED PANEL	2024	4	3903	1525
EXTRUDED PANEL	2025	5	171	-3732
MEDIAN BARRIER FOOTING (SHAFT)	2023	3	2498	0
MEDIAN BARRIER FOOTING (SHAFT)	2024	4	841	-1657
MEDIAN BARRIER FOOTING (SHAFT)	2025	5	3226	2385
SIGN PANEL OVERLAY	2021	1	4754	0
SIGN PANEL OVERLAY	2022	2	940	-3814
SIGN PANEL OVERLAY	2023	3	3887	2947
SIGN PANEL OVERLAY	2024	4	2247	-1640
SIGN PANEL OVERLAY	2025	5	2820	573
STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS	2022	2	2955	0
STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS	2023	3	4037	1082
STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS	2024	4	3755	-282
STRUCTURAL STEEL - BRIDGE MOUNTED TRUSS	2025	5	1619	-2136
STRUCTURAL STEEL - I-BEAM SIGN	2022	2	2875	0
STRUCTURAL STEEL - I-BEAM SIGN	2023	3	4216	1341
STRUCTURAL STEEL - I-BEAM SIGN	2024	4	1219	-2997
STRUCTURAL STEEL - I-BEAM SIGN	2025	5	920	-299
STRUCTURAL STEEL - MONOTUBE SIGN	2021	1	3415	0
STRUCTURAL STEEL - MONOTUBE SIGN	2022	2	3731	316
STRUCTURAL STEEL - MONOTUBE SIGN	2023	3	3598	-133
STRUCTURAL STEEL - MONOTUBE SIGN	2024	4	2313	-1285
STRUCTURAL STEEL - MONOTUBE SIGN	2025	5	4129	1816
STRUCTURAL STEEL - OVERHEAD SIGN POST	2022	2	2862	0
STRUCTURAL STEEL - OVERHEAD SIGN POST	2023	3	672	-2190
STRUCTURAL STEEL - OVERHEAD SIGN POST	2024	4	2238	1566
STRUCTURAL STEEL - OVERHEAD SIGN POST	2025	5	2590	352
STRUCTURAL STEEL - OVERHEAD SIGN TRUSS	2022	2	352	0
STRUCTURAL STEEL - OVERHEAD SIGN TRUSS	2023	3	4203	3851
STRUCTURAL STEEL - OVERHEAD SIGN TRUSS	2024	4	4610	407
STRUCTURAL STEEL - OVERHEAD SIGN TRUSS	2025	5	1933	-2677
STRUCTURAL STEEL - PANEL MOUNTING POST	2022	2	4939	0
STRUCTURAL STEEL - PANEL MOUNTING POST	2023	3	3416	-1523
STRUCTURAL STEEL - PANEL MOUNTING POST	2024	4	2892	-524
STRUCTURAL STEEL - PANEL MOUNTING POST	2025	5	2418	-474

Can anyone please help figure this out in the simplist way possible!!! Either in Power Query or with DAX outside of Power Query.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = Data[Avg. Awarded PPU]-COALESCE(LOOKUPVALUE(Data[Avg. Awarded PPU],Data[Fiscal Year],CALCULATE(MAX(Data[Fiscal Year]),FILTER(Data,Data[ITEM_DESCRIPTION]=EARLIER(Data[ITEM_DESCRIPTION])&&Data[Fiscal Year]<EARLIER(Data[Fiscal Year]))),Data[ITEM_DESCRIPTION],Data[ITEM_DESCRIPTION]),Data[Avg. Awarded PPU])

Hope this helps.

[ACraig08]

I broke it up into seperate columns to make it easier to calculate the percent change:

Previous Avg. = 

COALESCE(LOOKUPVALUE(Data[Avg. Awarded PPU],Data[Fiscal Year],CALCULATE(MAX(Data[Fiscal Year]),FILTER(Data,Data[ITEM_DESCRIPTION]=EARLIER(Data[ITEM_DESCRIPTION])&&Data[Fiscal Year]<EARLIER(Data[Fiscal Year]))),Data[ITEM_DESCRIPTION],Data[ITEM_DESCRIPTION]),Data[Avg. Awarded PPU])

 Change = Average - Previous Avg.

% Change = Change / Previous Avg.

Your code was helpful, thank you.

[Ashish_Mathur]

You are welcome.

[Sebaslop90]

Hey ACraig08,

I found a little tricky way to do this from power query, I hope I can explain myself good enough.

• Duplicate your original table
• On the original table add a new Index Column starting from 0.
• On the duplicated table add a new Index Column starting from 1.
• Now on the original table you can go to Combine tables and you use the new index as key to join them, on the original table you have your row 2 as 1, but on the duplicated one you have the row 2 as 1 so you can combine them and expand the avg ppu column moved one row.
• Just delete the New Index and you should be good to go
  

  
  I hope this helps