Forum Discussion

jiglow3501's avatar
jiglow3501
Frequent Visitor
7 years ago
Solved

difference between two values in same column

Year    T otal Sales       Difference in Sales from Last Year (should be result) 2014    0 2015   2.5                          2.5 2016   4.5                           2 2017   9                 ...
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi jiglow3501，

     

    Please create the calculated column with the formula below.

     

    Column =
    VAR temp =
        CALCULATE (
            MAX ( 'table'[Total Sales] ),
            FILTER ( 'table', 'table'[Year] = EARLIER ( 'table'[Year] ) - 1 )
        )
    RETURN
        IF ( 'Table'[Total sales] - temp = 0, BLANK (), 'Table'[Total sales] - temp )

    Then you will get the output below.

     

     

     

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi jiglow3501，

 

Please create the calculated column with the formula below.

 

Column =
VAR temp =
    CALCULATE (
        MAX ( 'table'[Total Sales] ),
        FILTER ( 'table', 'table'[Year] = EARLIER ( 'table'[Year] ) - 1 )
    )
RETURN
    IF ( 'Table'[Total sales] - temp = 0, BLANK (), 'Table'[Total sales] - temp )

Then you will get the output below.

 

 

 

 

Best Regards,

Cherry

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-piga-msft

 

I am working with a table with daily quantities.

 

What would be the formula for a calculated column with the daily difference of the quantities?

 

e.g.

 

Date           Qty     Diff

6/1/2020    1

6/2/2020    2          1

 

Thank you!

 

Daniel