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
Advocate IMarcelBeug 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!
Community ChampionJust 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).