Forum Discussion

jiglow3501's avatar
jiglow3501
Frequent Visitor
7 years ago
Solved

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)

  • 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. 

4 Replies

  • jiglow3501's avatar
    jiglow3501
    Frequent Visitor
    7 years ago

    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'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. 

  • 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