Subtract values from different rows

[foyiq]

Dear contributors,

I have a query result table which includes data from a folder in a following format. It looks approximately as follows:

Quarter	Year	Status	Item	Value
Q1	2016	budget	expense item	x
Q2	2016	budget	expense item	x
Q3	2016	budget	expense item	x
Q4	2016	budget	expense item	x
Q1	2016	actual	expense item	x
Q2	2016	actual	expense item	x
Q3	2016	actual	expense item	x
Q4	2016	actual	expense item	x
Q1	2017	budget	expense item	x
Q2	2017	budget	expense item	x
Q3	2017	budget	expense item	x
Q4	2017	budget	expense item	x
Q1	2017	actual	expense item	x
Q2	2017	actual	expense item	x
Q3	2017	actual	expense item	x
Q4	2017	actual	expense item	x

There are many other columns, which I took out for simplicity; many separate expense items, from which I will take only some in visuals later on.

The problem is that Actual values come in cumulative terms, whereas in Budget values come 'cleaned'. What I need, thus, is to be able to compare between Budget and Actual via respective visualisations (which I will do further by myself).
Now the problem is to get the Actual net figure for Q2 and Q3 inside a year (Q1 is already clean, and Q4 is yearly data). I am not being able to use correct combination of filters for calculate, as later in visuals I am using date filters (I have a separate date calendar, which I will relate to this one). Maybe it is possible to accomplish the task on a query level, but I am having trouble with this way, too.

The matter is quite urgent and your help would be highly appreciated.

Thank you beforehand.

[v-ljerr-msft · Accepted solution]

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. 

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

[MarcelBeug]

If 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).