Forum Discussion
Solved
Creating a Percent Change Column in Power Query
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 Quer...
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.
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.
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.
You are welcome.