Difference between two columns or rows

[jiglow3501]

Year	Total Sales	Difference in Sales from Last Year (should be result
2014	0
2015	2.5	2.5
2016	4.5	2
2017	9	4.5
2018	0	-9
2019	2	2

Find the difference within same column using DAX (result should be third column)

[jiglow3501]

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                             4.5
2018   0                             -9
2019   2                               2

How do you write dax for it? 

[ChrisMendoza]

@jiglow3501,

You could try something like:

Rank = RANKX(Table1,Table1[Year],,ASC,Dense) 

then 

Diff in Sales LY = 
LOOKUPVALUE ( Table1[Total Sales], Table1[Rank], Table1[Rank] )
    - LOOKUPVALUE ( Table1[Total Sales], Table1[Rank], Table1[Rank] - 1 )

[jiglow3501 · Accepted solution]

Rank = RANKX(flu,flu[Year],,ASC,Dense)

difference Ly = LOOKUPVALUE(flu[Total Flu Patients],flu[Rank],flu[Rank]) 

                              - LOOKUPVALUE(flu[Total Flu Patients],flu[Rank],flu[Rank] - 1)

with second dax formula 

i get message stating "table of multiple values was supplied where single value was expected"

I am calculating based on single year and i do not have date column or anyfurther drilldown on year.

your help is greatly appreciated. 

[v-yulgu-msft]

Hi @jiglow3501,

For calculated column:

Difference in Sales from Last Year =
VAR previousYearTotal =
    CALCULATE (
        LASTNONBLANK ( Table5[Total Sales], 1 ),
        FILTER ( Table5, Table5[Year] = EARLIER ( Table5[Year] ) - 1 )
    )
RETURN
    Table5[Total Sales] - previousYearTotal

For a measure displayed in a visual:

Difference in Sales from Last Year measure =
MAX ( Table5[Total Sales] )
    - CALCULATE (
        MAX ( Table5[Total Sales] ),
        FILTER ( ALLSELECTED ( Table5 ), Table5[Year] = MAX ( Table5[Year] ) - 1 )
    )

Best regards,

Yuliana Gu