Forum Discussion
Advocate ISubtract values from different rows
Hi foyiq,
According to your description above, you should be able to use the formula below to create a new calculate column in your table to get the Actual net figure for Q2 and Q3 inside a year. :smileyhappy:
Value2 = VAR pq = IF ( Table1[Quarter] = "Q2", "Q1", "Q2" ) RETURN IF ( Table1[Status] = "budget" || Table1[Quarter] = "Q1" || Table1[Quarter] = "Q4", Table1[Value], Table1[Value] - CALCULATE ( MAX ( Table1[Value] ), FILTER ( ALL ( Table1 ), Table1[Year] = EARLIER ( Table1[Year] ) && Table1[Quarter] = pq ) ) )Note: Just replace "Table1" with your real table name.
Regards
Community ChampionIf I understand correctly, for actuals in Q2 and Q3 you need the value minus the previous value (if available, maybe some data start with Q2 or Q3), otherwise you need the value.
Solution in Power Query; a prerequisite is that the data is already sorted on quarters:
let
Source = Input,
Grouped = Table.Group(Source, {"Year", "Status", "Item"}, {{"AllData", each _, type table}}),
AddedNetValue =
Table.TransformColumns(Grouped,
{"AllData",
(Data) =>
let
Indexed = Table.AddIndexColumn(Data, "Index", -1, 1),
AddedNetValue = Table.AddColumn(Indexed,
"Net value",
each if [Status] = "actual" and
-1 < [Index] and
-1 < List.PositionOf({"Q2","Q3"},[Quarter])
then [Value] - Indexed[Value]{[Index]}
else [Value])
in
AddedNetValue
}),
Expanded = Table.ExpandTableColumn(AddedNetValue, "AllData", {"Quarter", "Value", "Net value"}, {"Quarter", "Value", "Net value"}),
Reordered = Table.ReorderColumns(Expanded,{"Quarter", "Year", "Status", "Item", "Value", "Net value"})
in
Reordered
- foyiq
MarcelBeug Thanks so much for quick response!
Sorry for being such a rookie, but in this exact format, unfortunately, I am not able to reproduce your solution. May I include the more inclusive sample, where all the columns are shown, and note that I already have it in a query mode, so i can just continue reshaping there.FileName Period Quarter Year Status Status2 Entity EntityCode EntityFullName Rate ItemCode ItemName Value Slicer PL budget 1Q 2017.xls Q1 2017 Q1 2017 Budget Quarterly shortname 11111 fullname 3.5 1156 Expenses 6548.35 Budget|Quarterly|shortname PL actual 1Q 2017.xls Q1 2017 Q1 2017 Actual Quarterly shortname 11111 fullname 3.37 1156 Expenses 9510.8 Actual|Quarterly|shortname PL actual 2Q 2017.xls Q2 2017 Q2 2017 Actual Quarterly shortname 11111 fullname 3.1 1156 Expenses 16410.8 Actual|Quarterly|shortname
Each file represents a period (quarter and year) and status (budget and actual). Inside are different items.
Do I need to sort it so that each next row shows next quarter (you told about prerequisite condition)?
I guess I can't just get this query result in excel format, so as to make it a source for new query, as you have shown.Thank you!
- MarcelBeug
Just to be sure, you can add a sort step after the inner let:
Sorted = Table.Sort(Data, {"Quarter"}), Indexed = Table.AddIndexColumn(Sorted, "Index", -1, 1),Power Query is case sensitive so you need to change "actual" (based on your first example data) in the code to "Actual" (based on your last example data).
My suggestion would be to delete the last 2 steps and recreate them with your real data.
Expand is just the double-arrow button at the right in the header of column "AllData": there you have to choose your columns.
Reorder: you can just drag columns and the reorder code will be created for you,
As finishing touch I renamed the steps so the code looks better in the Advanced Editor.
Actually I created my code in Excel and the output is in Excel, no problem (if you have the right version: I see xls's instead of xlsx's as file extension).
