Forum Discussion

foyiq's avatar
foyiq
Icon for Advocate I rankAdvocate I
9 years ago
Solved

Subtract values from different rows

  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 ...
Need Help
Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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