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)
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.
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.
Hi, can you please provide a solution on power editor as i have the same issue with big data on directquery mode. if possible to share the pbix file as well, it will be the best. thanks for your support.