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)
You could do Year-1, but I wasn't sure if there was a price for every year or not, so went for latest year.
Try this updated measure:
PreviousPrice =
VAR LatestYear = MAXX(BookPrices,BookPrices[Year])
VAR _PreviousYear = MAXX(FILTER(BookPrices, BookPrices[Year]<LatestYear),BookPrices[Year])
RETURN MAXX(FILTER(BookPrices, BookPrices[Year]=_PreviousYear),BookPrices[Price])
Try this updated measure:
PreviousPrice =
VAR LatestYear = MAXX(BookPrices,BookPrices[Year])
VAR _PreviousYear = MAXX(FILTER(BookPrices, BookPrices[Year]<LatestYear),BookPrices[Year])
RETURN MAXX(FILTER(BookPrices, BookPrices[Year]=_PreviousYear),BookPrices[Price])
Here is the result what i got, and still get nothing. Considering also that there is a certain year that the book sold out so there will be no price / "0"
- Aha, since you've got Year in your table, you'll need to use an ALL() filter to get that to display properly, or remove Year from the table visual.
I dont want to remove the year table since its needed. do you mind to elaborate what you mean by using all()?
######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: