Forum Discussion

jiglow3501's avatar
jiglow3501
Frequent Visitor
7 years ago
Solved

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 with...
  • jiglow3501's avatar
    jiglow3501
    7 years ago

    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's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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