Forum Discussion

madan27's avatar
madan27
Frequent Visitor
9 years ago
Solved

SUM current and Previous records

Hi,

 

I am having a table as below

 

YearDesignationSalary
2010Engineer50000
2010Sr.Engineer70000
2010A.Manager90000
2011Engineer55000
2011Sr.Engineer75000
2011A.Manager95000
2012Engineer65000
2012Sr.Engineer85000
2012A.Manager105000

 

In the table I am able to filter the records based on Designation (using Visual Level Filter option in Power BI)

 

For example I am filtered the designation as 'Enginner'

 

YearDesignationSalary
2010Engineer50000
2011Engineer55000
2012Engineer65000

 

Now I need to derive the result as below

 

YearDesignationSalarySum
2010Engineer500005000
2011Engineer55000105000
2012Engineer65000170000

 

I need to derive the SUM by adding the Current and Previous years salary. Kindly help me to get the above result table.

 

Thanks,

 

Corrected years.

Need Help
Show and Tell
Tips and Tricks
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    madan27

     

    Hi, try witt this DAX

     

    Salary running total in Year =
CALCULATE (
    SUM ( 'Table1'[Salary] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Designation] ),
        Table1[Year] <= MAX ( Table1[Year] )
    )
)

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi madan27

     

    In the expected output you have two Engineer records for 2011.  Should one of these be for 2012?

     

    • madan27's avatar
      madan27
      Frequent Visitor
      9 years ago

      Yes Phil, your correct. Sorry for the typo....

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    madan27

     

    Hi, try witt this DAX

     

    Salary running total in Year =
CALCULATE (
    SUM ( 'Table1'[Salary] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Designation] ),
        Table1[Year] <= MAX ( Table1[Year] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Vvelarde That is going to give a running total of ALL years.  I read the requirement as current year, plus 1 year ago?  I could be nuts, but if not...

       

      Maybe something like... ?

       

      Salary CY :=
      CALCULATE (
          SUM ( Table1[Salary] ),
          FILTER (
              ALLEXCEPT ( Table1, Table1[Designation] ),
              Table1[Year] = MAX ( Table1[Year] )
          )
      )

      Salary PY := 
      CALCULATE (
          SUM ( Table1[Salary] ),
          FILTER (
              ALLEXCEPT ( Table1, Table1[Designation] ),
              Table1[Year] = MAX ( Table1[Year] ) - 1
          )
      )

      Salary CY+PY := [Salary CY] + [Salary PY]

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi,

     

    Anonymous, I agree with Vvelarde that madan27 needs to calculate the SUM by adding the Current and all Previous years' salary(not just the last previous year). :smileyhappy:

     

    madan27, the formula provided Vvelarde above should work in your scenario. Just replace "Table1" with your real table name, and use the formula to create a new measure, then show it on the Table visual with other corresponding columns.

     

    Regards