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. 

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
7 years ago

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's avatar
jiglow3501
Frequent Visitor
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.