cancel
Showing results for
Did you mean:
Frequent Visitor

## Difference between two columns or rows

 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)

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: find difference within same column

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.

4 REPLIES 4
Frequent Visitor

## find difference within 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                             4.5
2018   0                             -9
2019   2                               2

How do you write dax for it?

Established Member

## Re: find difference within same column

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 )```
Community Support Team

## Re: Difference between two columns or rows

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: find difference within same column

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.