cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jiglow3501 Frequent Visitor
Frequent Visitor

Difference between two columns or rows

YearTotal SalesDifference in Sales from Last Year (should be result
20140 
20152.52.5
20164.52
201794.5
20180-9
201922

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
jiglow3501 Frequent Visitor
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.

 

your help is greatly appreciated. 

4 REPLIES 4
jiglow3501 Frequent Visitor
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? 

ChrisMendoza Established Member
Established Member

Re: find difference within same column

@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 )
Community Support Team
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.
jiglow3501 Frequent Visitor
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.

 

your help is greatly appreciated.