Forum Discussion
Solved
Getting Subtraction from 1 column with certain criterias
Hi everyone, I really need your help to create a new substraction column in power query editor. I am new to power bi and never use M language before. This is the the photo of the data that i ha...
- Welcome to Power BI! There's a few ways you could do this. Is there a reason your result needs to be in a calculated column? I might recommend using a measure for this and using some DAX Time Intelligence features.
If you want it in Calculated Column, does it need to be done in Power Query, or would it be acceptable in DAX? (Depends what you need it for).
You could try as a New COLUMNS in Power BI or in Power Pivot Data model if working in Excel (not in Power Query Editor):
Previous Year Price = MAXX(FILTER(Table, EARLIER(Table[Year])<Table[Year] && Table[Genre] = EARLIER(Table[Genre])), Table[Price])
Price Difference = IF(ISBLANK(Table[Previous Year Price]), 0, Table[Price] - Table[Previous Year Price])
Note the calculation for Previous Year Price assumes the price is always increasing, so takes the maximum price from all previous years. 
######EDITED############
try using this code power query:
yourTab = Source{[Item="Foglio1",Kind="Sheet"]}[Data], breakTab=(tab as table) => let tabSorted=Table.Sort(tab,{"Price"}), breakList=List.Accumulate(List.Numbers(1,Table.RowCount(tabSorted)-1), {},(s,c)=>s&{tabSorted[Price]{c}-tabSorted[Price]{c-1}}) breakTab=(tab as table) => let tabSorted=Table.Sort(tab,{"Year"}), breakList=List.Accumulate(List.Numbers(0,Table.RowCount(tabSorted)), {},(s,c)=>s&{try (tabSorted[Price]{c}-tabSorted[Price]{c-1}) otherwise 0}) in Table.FromColumns({tab[Year],tab[Price],breakList},{"Year","Price","difference"}), #"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Book", type text}, {"Genre", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}, {"Difference", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Book", "Genre"}, {{"diff", each breakTab(_)}}), #"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"}) in #"Expanded diff"could get somethoink like this:
- Try this formula with ALL() and CALCULATE:
PreviousPrice =
VAR _CurrentYear = MAX(BookPrices[Year])
VAR _PreviousYear = CALCULATE(MAX(BookPrices[Year]), ALL(BookPrices[Year]), BookPrices[Year]<_CurrentYear)
RETURN
CALCULATE(SELECTEDVALUE(BookPrices[Price]), BookPrices[Year]=_PreviousYear)
######EDITED############
try using this code power query:
yourTab = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
breakTab=(tab as table) =>
let
tabSorted=Table.Sort(tab,{"Price"}),
breakList=List.Accumulate(List.Numbers(1,Table.RowCount(tabSorted)-1), {},(s,c)=>s&{tabSorted[Price]{c}-tabSorted[Price]{c-1}})
breakTab=(tab as table) =>
let
tabSorted=Table.Sort(tab,{"Year"}),
breakList=List.Accumulate(List.Numbers(0,Table.RowCount(tabSorted)), {},(s,c)=>s&{try (tabSorted[Price]{c}-tabSorted[Price]{c-1}) otherwise 0})
in
Table.FromColumns({tab[Year],tab[Price],breakList},{"Year","Price","difference"}),
#"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Book", type text}, {"Genre", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}, {"Difference", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Book", "Genre"}, {{"diff", each breakTab(_)}}),
#"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"})
in
#"Expanded diff"
could get somethoink like this:
Hi, i see trhis is can solve my issue as well but i am not expert in power bi so i appreciate your support to provide me by the pbix file so i can understand more what steps you did so i can follow. thanks for your support.